Make a cell change colors based on value of an other cell

[cristian91493]

I'm trying to change the color of a merged cell (C2) from red to green based on the value of an other cell (E2) and I don't get an error but it also doesn't work. The cells need to be variable rows because I'm making a template for data entry. If cell E2 has a 0 in it then C2 should turn red and if E2 has a 1 in it then C2 should turn green

I've tried isolating all the variables in in my problem and line by line commenting them out to isolate the actual problem. I also specified the sheet that the columns and ranges were on but it didn't seem to do anything although it didn't throw an error. I did get something to work on a test worksheet but it wasn't with the variable rows.

The code i have isolated is

Sub setCondFormat()

topRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'chooses the first row of the next template input
bottomRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(10, 0).Row
'chooses the last row of the next template input
stageOneEnd = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row
'sets end parameter for the first stage cell

Sheet1.Range("C" & topRow & ":C" & stageOneEnd).Merge
Sheet1.Range("C" & topRow & ":C" & stageOneEnd).VerticalAlignment =     xlCenter
Sheet1.Range("C" & topRow & ":C" & stageOneEnd).HorizontalAlignment =     xlCenter
'Start merginging and center column C

Sheet1.Range("E" & topRow & ":E" & bottomRow).Merge
Sheet1.Range("E" & topRow & ":E" & bottomRow).VerticalAlignment = xlCenter
Sheet1.Range("E" & topRow & ":E" & bottomRow).HorizontalAlignment =     xlCenter
'End merging and center column E

Sheet1.Range("C" & topRow & "").Interior.ColorIndex = 3
'sets up default color for the C column which is red (stage)

Sheet1.Range("C" & topRow & ":C" & stageOneEnd).Value = "Picture Taken"
'inputs label into the first stage (Picture Taken)

Sheet1.Range("E" & topRow & "").Value = 0
' inputs a zero value into column E to ensure first stage of column C starts off as red
    Sheet1.Range("E2").Select
    'selects cell E2, I think this is not important at all but is used as a gateway to conditional formatting
    With Sheet1.Range("C" & topRow & ":C" & bottomRow & "")
    'this selects the range that will change due to the conditional formatting
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
           "IF(Sheet1.(E" & topRow & ")=0,FALSE,TRUE)"
           'the condition for which the stage will change
           ' if the top cell in column E is equal to 0 then the formatting will not go through but if it isnt equal to zero then it will
            With .FormatConditions(.FormatConditions.Count)
                .SetFirstPriority
                With .Interior
                    .PatternColorIndex = xlAutomatic
                    .Color = 5287936
                    .TintAndShade = 0
                    ' the new formatting of cell in the C column
                End With
            End With
    End With
End Sub

Any help is greatly appreciated. I'm going crazy trying to figure this out.

 

[Bobjg]

Hi,

I do a very similar function see example (in column H based on the date value in F subtracting from today() ). I change the color with conditional formatting based on the days left until a contract expires based on the code below that is located in the output cells in column H. See Images below of the conditional formatting setups for each of the different color codes.
=IFERROR(INDEX({"Expired","30-","30+","60+"},MATCH(F36-TODAY(),{-10000,1,30,60})),"")

Hope this helps and not to confusing.

 

[cristian91493]

Hi bobjg,
Thanks for your help but I'm trying to make a data entry template that tracks progress. The rows are gonna change and it will grow to be over a thousand rows so i would need to do this through VBA. Thank you for your response though