Conditional formatting

[DarkoDeign2]

Hi,

I have conditional formatting of cells A2 to G2 (change fill color to blue if H2 contains the word "Stop".
Formula for formatting =$H2="Stop"
Applies to range =$A2:$G2

But what can I do to have the cell G2 to keep being blue even if H2 no longer has the text "Stop"?
Should I have a separate conditional formatting formula on for cell G2?

 

[Joe4]

Conditional Formatting can only run based on the current cell values. It has no memory of what they were before.

So do something like you want, you will either need to have a way to identify that the value in column H was "Stop" at one time (maybe a helper column, or some sort of "flag" in another column on the same row), or handle it manually or via VBA.

For example, instead of using Conditional Formatting, you could use VBA to watch the value in cell H2, and when it is set to "Stop", it will permanently color the other cells blue.
Since it is permanent coloring, it won't disappear if the value in H2 is changed after the fact.

Let us know if you want to pursue an option like that and need help setting it up.
Just let us know how the value in cell H2 is being updated (manually, or by formula).

 

[DarkoDeign2]

Hi,

I didn't know that conditional formatting is based on the current values, but it makes sense that it should be like that.
Yes I would appreciate a VBA code for this.

 

[Joe4]

Hi,

I didn't know that conditional formatting is based on the current values, but it makes sense that it should be like that.
Yes I would appreciate a VBA code for this.

OK, can you just answer this question, as it will affect how I write the code.

Just let us know how the value in cell H2 is being updated (manually, or by formula).

 

[Joe4]

If the value in H2 is NOT the result of a formula (and is manually being updated), then do the following:
Right-click on the user tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if cell H2 is not updated
    If Intersect(Target, Range("H2")) Is Nothing Then Exit Sub
    
'   Check the value of cell H2 and color if A2:G2 if met
    If Range("H2").Value = "Stop" Then
        Range("A2:G2").Interior.Color = 15773696
    End If
    
End Sub

Now, if cell H2 is manually updated to "Stop", cells A2:G2 will be filled with a blue fill, and will stay that way, even if the value in cell H2 is changed again.

 

[DarkoDeign2]

Hi,

Thank you for the answer.
The value H2 is updated manually.
But I have values in Column H from H2:H1500.
Also for I have values in Column A and G from row 2 until row 1500.

I tried to run the code but I get a compilation error.
Could be that I have another code aswell..

 

[Joe4]

Hi,

Thank you for the answer.
The value H2 is updated manually.
But I have values in Column H from H2:H1500.
Also for I have values in Column A and G from row 2 until row 1500.

I tried to run the code but I get a compilation error.
Could be that I have another code aswell..

Ah, critical details you didn't tell us until now! It is important to try to be complete with all your detail, and not oversimplify your question, otherwise you may get answers for the exact question you asked, but won't work for the actual problem you are trying to solve (because we only know what you decide to share with us).

This is code that will run on H2:H1500:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   Get all updated cells in column H
    Set rng = Intersect(Range("H2:H1500"), Target)
   
'   Exit if column H is not updated
    If rng Is Nothing Then Exit Sub
   
'   Check the value of cell H and color in A:G if met
    For Each cell In rng
        If cell = "Stop" Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "G")).Interior.Color = 15773696
        End If
    Next cell
   
End Sub

 

[DarkoDeign2]

Thank you Joe! I'll be more precise in my explanations from now on

 

[Joe4]

You are welcome.