Permanent Highlight using Conditional Formatting

[BenNV]

I have a value in D1 (which is constantly/automatically changing), If this value in D1 matches, say, E5, then I want the row to change colour to Orange, permanently.

Because D1 is changing, the cell does not seem to stay highlighted - is there anyway round this.

Also, I would like the whole row (from A5 to L5) to be highlighted, but this does not seem to work, eventhough I select all rows when setting the conditional format.

[Dave Hawley]

Hi Ben


If you want this change to stick permantely you will need VBA. Right click on the sheet name tab and select "View Code" now paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Rows(5).Interior.ColorIndex = 46 Then End

    If Target.Address = "$D" And Range("D1") = Range("E5") Then
        Rows(5).Interior.ColorIndex = 46
    End If

End Sub

[BenNV]

Good Morning Dave,

I tried your suggestion but it didn't seem to work. I new to VBA, so maybe I overlooked something.

The only thing is, I want condition to be met automatically, i.e without running a macro, etc.

Thanks in advance,

Ben

[Dave Hawley]

Hi Ben

This code will happen automatically as soon as the user types a value into D1 that is = to E5.

Oops, just looked at the code and it seems I ommited the 1 from "$D$1"

Private Sub Worksheet_Change(ByVal Target As Range)
If Rows(5).Interior.ColorIndex = 46 Then End

If Target.Address = "$D$1" And Range("D1") = Range("E5") Then
Rows(5).Interior.ColorIndex = 46
End If

End Sub


You will also need to remove any conditional formatting for row 5

[BenNV]

Aha! Works fine, when I change cell D1. However, because D1 changes automatically without user interference - the code doesn't seem to work, even though D1 = E5.

Anymore suggestions?

[BenNV]

HELP!

[BenNV]

Another thought; can I add a macro to the conditional format and do it that way?

[Dave Hawley]

Hi Ben


Just remove the code:
"Target.Address = "$D$1" And"

From the original macro.

[BenNV]

Hi Dave,

Thats fantastic! Thank you very much. I've got the code to highlight when D1 = E5.

However, if focus is not on the cell/workbook, the highlight does not seem to work - is there a way of getting round this?

Once again, thanks for your help so far.

Ben

[Tom Schreiner]

Perhaps this code needs to be placed in the Calculate event?