Replicating conditional formatting in Excel

Question

I'm having trouble using Conditional Formatting in Excel. No problem with setup for one row, but no luck replicating it to the rest of the worksheet.

         A            B         C             D
1    MATCH_RESULT   CODE    DESCRIPTION    CHECKUP                  
2       #N/A         A1       Chair         #N/A
3       #N/A         B1       Window        #N/A
4         2          C1       Table         #N/A

The goal is to identify if column A is filled with value #N/A (default error message for MATCH function). If it is, cells in columns - A, B, C - ONLY ON THE SAME ROW should be formatted (with color red). I.e. as per example, A2:C3 should be formatted.

This is working for me, however just for the first row and I fail to correctly replicate this downwards.

I'm selecting A2:C2 -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format.

I will end up with the formatting setup such as:

Formula: =$A$2=$D$2 Applies to: =$A$2:$C$2

A2 through C2 will be formatted, as expected (due to A2=D2). However I can't replicate the same thing on next rows. Format painter function will expand the current functionality so that still A2=D2 match is looked at for formatting other rows, instead of A3=D3 and so on.

Pretty sure the key is in $, but nothing has worked yet. Also pretty sure Excel allows just one cell in column D to be populated with value #N/A for comparison.

pnuts pnuts · Accepted Answer · 2017-11-05T20:30:04

You have three issues, first your range is (part of) a single row =$A$2:$C$2. Try selecting ColumnsA:C before adding the rule, or change Applies to to:

=$A:$C

Second, you have anchored the row references in your formula. The 'style' should be:

=$A1=$D1

However your third issue is that comparing errors (#N/A resulting from a function) returns ... #N/A (so I don't know how you managed to get even a single row to work, since CF needs a TRUE result to trigger).

Maybe try:

=AND(ISNA($A1),ISNA($D1))

Replicating conditional formatting in Excel

2 Answers