vba conditional formating based on a different column

0
votes

I have range "A1: B10". I want to apply conditional formatting to range A1:A10 such that if cell B in that row contains text "ok" then apply some format to cell A in that row.

Example:

A        B
text    ok
text    other text
text    ok

cells A1 and A3 will be formatted by the conditional formatting setting. I understand how to use FormatConditions.Add when it comes to testing the same cells which I want to format but not when it is based to a condition in other cells.

Thanks

2
excelvbaconditional-formatting
You know you don't need VBA for this, right? Conditional formatting knows this by default, so why not just record a macro while setting it up? - vacip
I gave a simple example just to understand how to use formatConditions. In my macro the conditions are much more complicated and recording gives me too much mess - Hana

2 Answers

0
votes

When referencing cells other then the one being formatted, pay attention to the absolute-relative references.

Also note the double quotes escaping the quotation mark.

Range("A1:B3").FormatConditions.Add Type:=xlExpression, Formula1:="=$B1=""ok"""
0
votes

To base the conditional formatting on other cells, reference the other cells in the formula. 

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=NOT(ISERROR(SEARCH(""ok"", B1)))"

The Offset function works in the formula, too.

Range("A:A").FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=OFFSET(A1, 0, 1)="ok""