Dynamic row & column highlights in a data table

[Naz555]

Hi Peeps,

Happy new year to you all!

I have a two way data table for which the two variables are changed frequently. In the data table, for ease of reference, i usually highlight the corresponding row and column of the two most current variables the intersection of which gives you the current output. But these highlights need to eb changed each time the variables are changed 9the table is copied onto another document for presentation hence the need to keep changing the highlights).

I was thinking of configuring the highlighting process to happen dynamically, i.e. the row and column highlights change dynamically when the variables are changed. Could any of you suggest how this could be done?

Ive also attached an excel working exemplifying my situation.

Cheers,

Naz

[FDibbins]

Try this...

1. highlight the range you want to apply the conditional formatting to...G5:L15
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter ==G$5=$D$5..................format fill YELLOW

Repeat 3 & 4 with =$G6=$D$6..................format fill YELLOW

[Naz555]

Hi mate, thanks for the reply. The thing is, my datables are quite large and following your suggestion will be quite onerous. Hence the quest for a dynamic solution that does the needful.

Cheers.

[FDibbins]

The thing is, my datables are quite large and following your suggestion will be quite onerous

How so? once yoy set the ranges and the rules, that should be it? Thwe highlighting will move automatically as you change the values in D5 and D6. Maybe increase the ranges to include 1 extra (blank) ro and column, then insert new rows/columns inside those, if needed?

The only other way around this would be VBA

[Naz555]

Sorry, I misread your initial suggestion, and i re-tried. But it stil doesnt work for me. if its not asking for too much, would you mind sharing it on excel please?

[FDibbins]

sure, here you go. test by changing the values in D5 and D6

[Naz555]

Fantabulous! Thanks a zillion mate, your a legend!!

[FDibbins]

lol not quite, but thanks for the kind waords and feedback, always appreciated