Conditional Formatting using cell 'address' column

[Mrgoodkat]

Hi all,

I currently have a large data range which I will simplify as A1:F6 as shown below.

In the G column I have a list of cell addresses calculated from numerous previous formulas referring to specific cells in the aforementioned data range within the same row.

I am looking for a way to use conditional formatting to simply outline the cell addresses listed in the G column.

I cannot simply write out each cell address in the 'Applies to' section of conditional formatting as this spreadsheet will serve as a template and needs to automatically highlight the cells in the G column. In addition to this there is such a large list that to simply write each one out would be a large waste of time.

I have tried to concatenate all the cells in the G column and then use this in the 'Applies to', however this simply lists the very first cell in the G column (E1 in the below example).

It is also worth noting that I cannot do multiple conditional formats as this will result in over 300 separate instances and as a template I am trying to keep the spreadsheet as sharp as possible

So, any ideas?


[TABLE="width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[TD="align: center"]Cell Addresses[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$E$1[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$B$2[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$F$4[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$A$5[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$C$6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Select top left corner cell

Conditional Formatting
New Rule
Use a formula to determine...

=NOT(ISNA(MATCH(ADDRESS(ROW(),COLUMN()),G$1:G$6,0)))
format as required

Use Format painter to copy to other cells

 

[Mrgoodkat]

Thank you very much, this works a charm.

This is a pretty great community!