Dynamic conditional formatting

Question

I'm trying to find solution for dynamic conditional formatting.

For when the range has a 'FALSE' cell, highlight the value 4 columns ahead.

Example =G4='FALSE' --> format C4 cell.

However, I cannot specify the columns as I wouldn't know which column will have a 'FALSE' cell.

But there is a baseline format. E.g. the whole range is a pattern of 7 columns. The 2nd column of the range will the value to be highlighted and the 6th column of the range will have True/False.

Is there a way to check every 6th column for FALSE cell and highlight the value 4 columns ahead.

Any suggestions?

Below is a sample of how my excel looks like

Thank you xoxo

If you temporarily change to R1C1 mode then you can use a relative reference to create your conditional formatting. You can always flip back to A1 mode after it's set up. IMHO though, R1C1 mode is much easier to work with in general and I leave mine in that mode all of the time. — Enigmativity

Dan Donoghue Dan Donoghue · Accepted Answer · 2018-10-03T03:40:05

Are you wanting it to update as the values are entered? if so then you could use a the Worksheet_Change event, it's a bit crude but would work.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = "False" Then
        With Target.Offset(0, -3).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 5287936
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
End Sub

Need more details to assist you further, if you want something you can run on an as needs basis then it should be fairly trivial for you to convert this to a sub routine as opposed to an event, just poll through the used data.

Based on your comments, something like this will do what you want:

Sub Temp()
Dim X As Long
For X = 1 To Range("E" & Rows.Count).End(xlUp).Row
    If Range("E" & X).Value = "False" Then
        With Range("E" & X).Offset(0, -3).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 5287936
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End If
Next
End Sub

Change "E" in both instances of the Range if you want it to poll another column.

Dynamic conditional formatting

1 Answers