VBA Excel Format Range when value is found

Question

I'm trying to implement a macro that looks for the words "TRUE" and "FALSE" in a huge array of data - and then, when found, changes the color of the cells above it.

Specifically, I would like it to color not the TRUE/FALSE-cell, but the 30 cells directly above it. This is where things get tricky... I hope someone can help.

I've tried adapting the below code, but mostly I'm adding it as inspiration at this point.

Sub ChangeColor()
    lRow = Range("C" & Rows.Count).End(xlUp).Row
    Set MR = Range("C2:C" & lRow)
    For Each cell In MR
        Select Case cell.Value
            Case "Yes"
                cell_colour = 4
            Case "y"
                cell_colour = 4
            Case Else
                cell_colour = 3
            End Select
        cell.Interior.ColorIndex = cell_colour
    Next
End Sub

Answer 1

The code that I posted should only highlight cells in column B whose value is different from the corresponding cell in column A. I tested it and it worked OK.

If you want to try conditional formatting:

Select column B, or the part of column B that you want to colour conditionally.

In the following, I will assume that B1 is the active cell within the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select "Use a formula to determine which cells to format". Enter the formula =B1<>A1

If the active cell is not in row 1, adjust the formula accordingly. For example, if the active cell within the selection is B3, use =B3<>A3

Click Format...

Activate the Fill tab.

Select the desired fill colour.

Click OK until all dialogs have closed.

Change some values in column A and/or B to see the result.

Refer - https://social.technet.microsoft.com/Forums/ie/en-US/2fffa4d8-bbba-473b-9346-5fce8f0728a8/using-vba-to-change-a-cell-colour-based-on-the-information-in-a-different-cell-excel-2010?forum=excel

Answer 2

I would do this with conditional formatting

Mark all your data and press "Conditional Formatting". Enter 2 rules with Use a formula... First rule is for TRUE. Assuming that you start with Col A:

=COUNTIF(A2:A31;TRUE)

The second rule is similar, just exchange TRUE by FALSE. Below the formula, press the "Format" button to set the color.

Explanation: I reverted the logic: Instead of searching for TRUE/FALSE and then format the cells above, I look for every cell if it has at least one entry TRUE resp. FALSE in the next 30 cells. However, I have to admit I don't know how fast Excel can handle such a large amount of data.

Answer 3

First you need to check whether the row of the cell is higher than 30 and then it you can offset to change the color:

Thus instead of this line: cell.Interior.ColorIndex = cell_colour write this:

If cell.Row > 30 Then cell.Offset(-30, 0).Interior.ColorIndex = cell_colour

Answer 4

Using a datafield array

Looping through a range is always time consuming; this should speed it up.

Caveat: Formatting single cells can maximize file size, so at least I reformat the whole column C to xlColorIndexNone.

Option Explicit

Public Sub Mark30CellsAbove()
Dim ws   As Worksheet
Set ws = ThisWorkbook.Worksheets("MySheet")
Dim v    As Variant
Dim i    As Long, j As Long, n As Long, m As Long, r As Long
Dim Rng  As Range
Dim t    As Double
' stop watch
  t = Timer
' get last row in column C
  n = ws.Range("C" & ws.Rows.Count).End(xlUp).Row
' get values to one based 2dim array
  v = ws.Range("C1:C" & n).Value
' clear existing colors over the WHOLE column to minimize file size
  ws.Range("C:C").Interior.ColorIndex = xlColorIndexNone
' loop through C2:Cn and mark 30 rows before found condition
  For i = 2 To n
      ' check condition, find string "true" or "false"
        If InStr(".true.false.", "." & LCase(v(i, 1)) & ".") > 0 Then

            ' set range block - fixed rows count 30 above found cell
            If i < 32 Then      ' only in case of less than 30 rows
               Set rng = ws.Range("C2:C" & (i - 1))
            Else
               Set rng = ws.Range("C" & (i - 30) & ":C" & (i - 1))
            End If
            rng.Interior.ColorIndex = 4

        End If
  Next i
  MsgBox "Time needed: " & Format(Timer - t, "0.00") & " seconds."
End Sub

Of course you could also loop within If - EndIf, just to see this slower method:

        If InStr(".true.false.", "." & LCase(v(i, 1)) & ".") > 0 Then

            ' Always avoid to loop through a range
            ' For j = i - 1 To i - 30 Step -1
                ' If j < 2 Then Exit For      ' optional escape if one line under title row
                ' ws.Cells(j, 3).Interior.ColorIndex = 4
            ' Next
         End If

Answer 5

This may be done without VBA. You should set up two conditional formatting with formulas. First:

=COUNTIF(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),1,0,29,1), "TRUE")>0

and the same for false. To highlight the cell you just need to use Highlight Cell Rules (the top option for CF).