Excel 2007 Conditional Formatting is not properly using custom formula provided

conditional formattingmicrosoft-excel-2007

In Excel 2007, I want to conditionally color a row if it is odd numbered and then vary the coloring depending on if a specific cell (in column E) in that row contains a number (green) or empty(red). E.g. if E15 has a value of 2 and E13 has no entry, I would expect row 15 to be green and row 13 to be red.

My two formulas are:
To color red:
=IF((MOD(ROW(),2) = 1),NOT(ISNUMBER(INDIRECT("$E$"&ROW()))), FALSE)
To color green:
=IF((MOD(ROW(),2) = 1),ISNUMBER(INDIRECT("E"&ROW())), FALSE)

If I paste these formulas into cells on the worksheet I get the expected values. For row 15 the "red" equation is false and the "green" equation is true. For Row 13 the "red" equation is true and the "green equation is false. However if I use these formulas in the conditional formating use formula feature, all of my rows are red, any thoughts?

Best Answer

Your problem is INDIRECT. It is not playing nicely with your conditional formulas, which seems to be some sort of limitation around INDIRECT.

However, I don't think you need it. If I understand your requirement correctly, you can just change the green conditional formula to =IF((MOD(ROW(),2) = 1),ISNUMBER($E1), FALSE). The use of $E1 will force the formula to reevaluate for each row, so it turns into:

=IF((MOD(ROW(),2) = 1), ISNUMBER($E1), FALSE) for E1
=IF((MOD(ROW(),2) = 1), ISNUMBER($E2), FALSE) for E2
=IF((MOD(ROW(),2) = 1), ISNUMBER($E3), FALSE) for R3
etc.

Similarly, you can replace your red formula with =IF(MOD(ROW(),2) = 1,NOT(ISNUMBER("$E1)), FALSE)

Related Solutions

Excel: Dual graded color scales

All right, I got kind of sick of this question sticking out there. I wrote this macro, it sorts ascending, divides the non-blank range in half (rounded up) and applies two color scales. Modify as you wish.

Sub TestColorScale()

Application.ScreenUpdating = False

'sort ascending
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A:A")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'count cells
Dim intcount As Integer
Dim rngcount As Range
Set rngcount = Range("A:A")
intcount = Application.WorksheetFunction.CountA(rngcount)
rngcount.FormatConditions.Delete


'Get half
Dim inthalf As Integer
inthalf = intcount / 2
inthalf = Application.WorksheetFunction.RoundUp(inthalf, 0)


'Do first half
    Dim rng1 As Range
    Set rng1 = Range(Cells(1, 1), Cells(inthalf, 1))

   ' Add a 2-color scale.
    Dim cs1 As ColorScale
    Set cs1 = rng1.FormatConditions.AddColorScale(ColorScaleType:=2)

    ' Format the first color
    With cs1.ColorScaleCriteria(1)
        .Type = xlConditionValueLowestValue
        With .FormatColor
            .Color = vbGreen
            .TintAndShade = -0.25
        End With
    End With

    ' Format the second color
    With cs1.ColorScaleCriteria(2)
        .Type = xlConditionValueHighestValue
        With .FormatColor
            .Color = vbBlue
            .TintAndShade = 0
        End With
    End With

'Do second half
    Dim rng2 As Range
    Set rng2 = Range(Cells(inthalf + 1, 1), Cells(intcount, 1))
   ' Add a 2-color scale.
    Dim cs2 As ColorScale
    Set cs2 = rng2.FormatConditions.AddColorScale(ColorScaleType:=2)

    ' Format the third color
    With cs2.ColorScaleCriteria(1)
        .Type = xlConditionValueLowestValue
        With .FormatColor
            .Color = vbRed
            .TintAndShade = -0.25
        End With
    End With

    ' Format the fourth color
    With cs2.ColorScaleCriteria(2)
        .Type = xlConditionValueHighestValue
        With .FormatColor
            .Color = vbYellow
            .TintAndShade = 0
        End With
    End With

Application.ScreenUpdating = True

End Sub

Best Answer

Related Solutions

Excel: Dual graded color scales

Related Question