Dynamic Conditional formatting with a scroll bar

[Mattyastill]

Hi,


Im currently trying to apply conditional formatting to a table of data that changes via a scroll bar, the data ranges from years 2007-2016 consisting of Q1-Q-4.

The scroll bar i have also ranges from the same years. I would like the conditional formatting to highlight the highest and lowest values for each each depending on the value of the scroll bar.

So if the user has selected 2007 then the highest and lowest values from Q1-Q4 for the year will be highest green and red respectively.

I tried nested if statements but nothing seemed to happen and my code wouldn't save.

https://imgur.com/a/gwYAb This is the table i am trying to applying the formatting to.


https://imgur.com/a/jjwKV This is the dashboard i am creating and want to replace the graph is the bottom right with the above table.


Any help would be much appreciated as i am unable to move on with my project till this is solved.

Thanks and looking forward to hearing from you.

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe something like this

Select G13:J22

Rule 1 (highlight min)
Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format
insert this formula
=AND($F13=$B$9,G13=MIN(INDEX($G$13:$J$22,MATCH($B$9,$F$13:$F$22,0),0)))
pick the format you want (Fill --> light red, for example)

Rule 2 (highlight Max)
Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format
insert this formula
=AND($F13=$B$9,G13=MAX(INDEX($G$13:$J$22,MATCH($B$9,$F$13:$F$22,0),0)))
pick the format you want (Fill --> light green, for example)

Hope this helps

M.

 

[Mattyastill]

Thanks,

Wow it worked perfectly! Thanks so much for that.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.