Excel VBA Macro Conditional Formatting By Referencing Cell Pair Relative Location

Answer 1

AFAIR u can't work with worksheet_change because it doesn't fire if you only change the background color. The simplest solution is to add a button with the caption "highlight matrix" that walks through your sensordata and highlights the found rows in the matrix.

Private Sub highlightMatrix()
Dim SensorData As Range
Dim Matrix As Range
Dim yellowRows As Collection
Dim isYellow As Boolean
Dim iColumn As Integer

Set SensorData = Worksheets.Item(1).Cells(3, 1).CurrentRegion
Set Matrix = Worksheets.Item(1).Cells(3, 10).CurrentRegion
Set yellowRows = New Collection

For Each Row In SensorData.Rows     ' walk the used rows of sensordata '
    isYellow = False
    iColumn = 3

    While iColumn >= 3 And iColumn <= 8 And isYellow = False    ' identify rows with yellow marked sensordata '
        If Row.Cells(1, iColumn).Interior.ColorIndex = 6 Then
            isYellow = True
            yellowRows.Add (Row.Row)
        End If
        iColumn = iColumn + 1
    Wend
Next Row

Matrix.Interior.ColorIndex = xlNone  ' set matrix background to default '
For Each Item In yellowRows
    For Each Row In Matrix.Rows
        If Row.Cells(1, 1) = Worksheets.Item(1).Cells(Item, 1) And Row.Cells(1, 2) = Worksheets.Item(1).Cells(Item, 2) Then ' color found rows red '
            Row.Cells(1, 1).Interior.ColorIndex = 3
            Row.Cells(1, 2).Interior.ColorIndex = 3
            Row.Cells(1, 3).Interior.ColorIndex = 3
        End If
    Next Row
Next Item

Set yellowRows = Nothing

End Sub

Its not the most efficient way to solve this problem but it should be fine with small worksheets.

Adding more Sensors: The array/collection yellowRows stores the rownumbers of every key1/key2 combination that has at least one yellow sensor value. If you want to add other sensors, u could add the columns after the current 6 sensor rows (C - H) and set the matrix row to the new column position e. g. 13 instead of 10 and set iColumn <= 11 instead of 8 if u add 1 new sensor with 3 columns.

Adding more Matrices: To add more matrices u simply need to add a matrix in the given layout anywhere and define a new range for the matrix e.g.

Set Matrix2 = Worksheets.Item(1).Cells(100, 1).CurrentRegion 'Matrix 2 starts in the 100. row on the 1. spreadsheet in the 1. column'

then just copy+paste the for loop of your original matrix(and change Matrix.Rows in Matrix2.Rows) in the yellowRows loops (now u have 2 Loops in your yellowRows loop)

Regarding your Sample file:

• There was a "End Sub" at the start of the Sub that needed to be deleted
• The Matrix range was set wrong
• the sensordata should start at column

• Because you have an id column the line

     If Row.Cells(1, 1) = Worksheets.Item(1).Cells(Item, 1) And Row.Cells(1, 2) = Worksheets.Item(1).Cells(Item, 2) Then ' color found rows red '
  

  changes to

     If Row.Cells(1, 1) = Worksheets.Item(1).Cells(Item, 2) And Row.Cells(1, 2) = Worksheets.Item(1).Cells(Item, 3) Then ' color found rows red '
  

• the column loop should start at 5 and end at 16

Here is the modified Sample File: http://www.mediafire.com/?vkbyv1n4m0t