Excel Conditional Formatting by Formula isn't applying correct styles

Question

I have a formula used for conditional formatting:

=OR(ISNUMBER(FIND(Primary,INDIRECT(ADDRESS(ROW(),2)))),ISNUMBER(FIND(Secondary,INDIRECT(ADDRESS(ROW(),2)))),INDIRECT(ADDRESS(ROW(),2))="ALL")

The formula is intended to highlight a cell if a value in the "B" column matches the value in the Primary field, the Secondary field, or is set to "ALL".

When pasted into any cell in a row, it returns the appropriate TRUE or FALSE value, however when used as a formula for conditional formatting, no cells receive any formatting.

Scott Craner · Accepted Answer · 2018-01-25 16:01:13Z

1

get rid of the indirect and ROW():

=OR(ISNUMBER(FIND(Primary,$B1)),ISNUMBER(FIND(Secondary,$B1)),$B1="ALL")

Make the sure the row refers to the first row in the Applies to: range. So in this it is expeted that you are using full column reference or that the range starts on row 1.

If it is different then change the 1s to the first row in the applies to range.

answered Jan 25, 2018 at 16:01

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Conditional Formatting by Formula isn't applying correct styles

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related