CODEWITHSUNDEEP
excelconditional-formattingself-reference
Charles Duff
Charles Duff

Reputation: 5

Nested conditional formatting for self referencing cells

I am formatting an excel sheet and want to apply a conditional format to a range of cells for when the particular cell matches the one either above or below it, AND when the corresponding cell in the adjacent column matches the cell either above or below it. an example of the information in the columns:

Name Ref ID
PER 002-003
PPJ 021-006
PPJ 021-006
PPJ 021-006
RAD 005-013
REI 006-009
RIT 005-085
STJ 002-003
STJ 002-003
TEL 002-013
TEL 007-014
So in this section of the sheet PPJ & STJ should Highlight/fill but TEL should not since while the name matches the REF ID doesn't.

I have tried:

=AND(
    OR(
        INDIRECT(ADDRESS(ROW(), COLUMN())) = INDIRECT(ADDRESS(ROW(), COLUMN(),-1,0)), 
        INDIRECT(ADDRESS(ROW(), COLUMN())) = INDIRECT(ADDRESS(ROW(), COLUMN(),+1,0))
        ), 
    OR(
        INDIRECT(ADDRESS(ROW(), COLUMN(),0,+1)) = INDIRECT(ADDRESS(ROW(), COLUMN(),-1,+1)),
        INDIRECT(ADDRESS(ROW(), COLUMN(),0,+1)) = INDIRECT(ADDRESS(ROW(), COLUMN(),+1,+1))
    )
)

but get no response from the cells regarding the formatting changes. I have tried substituting offset for the second OR statement referring to the adjacent column but get an error about too many arguments.

Am I taking an incorrect approach to this or am I missing a step?

Upvotes: 0

Views: 513

Answers (1)

barry houdini
barry houdini

Reputation: 46371

You shouldn't need that level of complexity. For conditional formatting you can just apply the formula that applies to the top left cell of the range, so if your data is in D3:E14 for example then you only need to use the formula that applies to D3.....so follow these steps

Select the range to format (D3:E14 in my example)

Apply conditional formatting with the formula that applies to the top left cell of the range, i.e. for your problem

=AND(OR($D3=$D2,$D3=$D4),OR($E3=$E2,$E3=$E4))

Choose required format > OK > all done

Excel will implicitly adjust the conditional formatting formula for the whole "applies to" range as if you were dragging that formula on the worksheet, so it will work as required for the whole range

Given your sample data above here's the screenshot of that CF formula applied to your data:

enter image description here

Upvotes: 2

Related Questions