none
Conditional Formatting dependent on another cell RRS feed

  • Question

  • I'm not sure if VBA is needed but is there a way to do conditional formatting based on another cell?
    i'm trying to make the cell highlight red if "#N/A" appears 2 cells to the right of it.

        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ISNA(ActiveCell.Offset(0, 2))"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .Color = -16776961
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False

    I'm trying to get the conditional formatting to work on specific cells only rather than the whole sheet by putting something like the script above in the loop that is going through the cells that I need it in. Any ideas?

    • Edited by Damon Fong Wednesday, August 7, 2013 6:27 PM clarify question
    Tuesday, August 6, 2013 6:34 PM

Answers

  • If you apply it to all cells, it may slow down the worksheet.

    Select the range that you want to format conditionally.

    Proceed as above.

    In the formula =ISNA(G2), change G2 to the cell two columns to the right of the top left corner of the selected range. If you selected all cells (Ctrl+A), A1 will be the top left corner, so use C1 in the formula.


    Regards, Hans Vogelaar

    • Marked as answer by Damon Fong Wednesday, August 7, 2013 4:21 PM
    • Unmarked as answer by Damon Fong Wednesday, August 7, 2013 4:52 PM
    • Marked as answer by Damon Fong Wednesday, August 7, 2013 4:52 PM
    Tuesday, August 6, 2013 9:58 PM
  • Excel will automatically adjust the formula if the selection contains multiple cells. But you could use R1C1 notation to make it explicit:

        Selection.FormatConditions.Delete
        With Selection.FormatConditions.Add(Type:=xlExpression, Formula1:="=ISNA(RC[2])")
            .SetFirstPriority
            With .Font
                .Bold = True
                .Italic = False
                .Color = -16776961
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With


    Regards, Hans Vogelaar

    • Marked as answer by Damon Fong Wednesday, August 7, 2013 8:40 PM
    Wednesday, August 7, 2013 6:49 PM

All replies

  • No, you don't need VBA for that.

    Let's say that you want to format E2:E10.

    Select this range.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...

    Select "Use a formula to determine which cells to format".

    Enter the formula

    =ISNA(G2)

    Here, G2 is the cell to columns to the right of the first cell (E2) in the selection. Adjust as needed.

    Click Format...

    Specify the desired formatting.

    Click OK twice.


    Regards, Hans Vogelaar

    Tuesday, August 6, 2013 7:11 PM
  • would this work if I want to apply it to all cells?
    Tuesday, August 6, 2013 8:27 PM
  • If you apply it to all cells, it may slow down the worksheet.

    Select the range that you want to format conditionally.

    Proceed as above.

    In the formula =ISNA(G2), change G2 to the cell two columns to the right of the top left corner of the selected range. If you selected all cells (Ctrl+A), A1 will be the top left corner, so use C1 in the formula.


    Regards, Hans Vogelaar

    • Marked as answer by Damon Fong Wednesday, August 7, 2013 4:21 PM
    • Unmarked as answer by Damon Fong Wednesday, August 7, 2013 4:52 PM
    • Marked as answer by Damon Fong Wednesday, August 7, 2013 4:52 PM
    Tuesday, August 6, 2013 9:58 PM
  • Good Point. The column is dependent of the source of the information so I'll find a way for it to work on those specific columns only to avoid a slow worksheet.

    Thanks for the information Hans!


    • Edited by Damon Fong Wednesday, August 7, 2013 12:54 AM
    Wednesday, August 7, 2013 12:43 AM
  • I recorded a macro so the formula will be added only if the cell needs it. However, I recorded the macro at B2 which is why it showed D2. Is there an equivalent to say "2 cells over"?

      Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ISNA(D2)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Font
            .Bold = True
            .Italic = False
            .Color = -16776961
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False

    Wednesday, August 7, 2013 4:54 PM
  • Excel will automatically adjust the formula if the selection contains multiple cells. But you could use R1C1 notation to make it explicit:

        Selection.FormatConditions.Delete
        With Selection.FormatConditions.Add(Type:=xlExpression, Formula1:="=ISNA(RC[2])")
            .SetFirstPriority
            With .Font
                .Bold = True
                .Italic = False
                .Color = -16776961
                .TintAndShade = 0
            End With
            .StopIfTrue = False
        End With


    Regards, Hans Vogelaar

    • Marked as answer by Damon Fong Wednesday, August 7, 2013 8:40 PM
    Wednesday, August 7, 2013 6:49 PM
  • It is working! Thanks Hans, I am learning a lot the last couple of days.
    Wednesday, August 7, 2013 8:40 PM