Seperate color scale conditional formatting for data grab sheet

[skipper.c]

G'day,

I have about 200 data sheets and one control sheet or dashboard sheet. The control sheet uses a macro to grab the data sheet info for display, and contains color scale conditional formatting in two of the columns. The data displayed is of separate transactions that I want to be separately color scaled. The transactions are separated by a blank row. Because I have set the whole column for a color scale rule, all the transactions are not separately color scaled, but scaled over the whole column. Each time I grab the transactions they can be any where within the column, and separated by a blank row. The transactions below on the left are scaled over the whole column, but I need each transaction to be separately color scaled, as on the right. Is there a way to do this, Thanks.

Transaction colors 2.jpg

[sandy666]

Try to select ranges separately with Ctrl key

or

attach sample workbook

[skipper.c]

Try to select ranges separately with Ctrl key

or

attach sample workbook

Thanks for the reply sandy666,

Simply selecting the ranges then color scaling separately would be too onerous. I use the control sheet to call for data from the data sheets at least 100 times through out the day, and each time there could be 20 different transactions on the page. I thought maybe I could add a formula in the conditional formatting on the control sheet to somehow color scale separately the ranges that are filled. I could color scale the transactions on each data sheet, but I wanted to keep the data sheets as raw data only, with no formatting at all.

[sandy666]

Conditional Formatting works with defined range(s) and Boolean values: TRUE or FALSE so you can't define range by formula inside CF

Maybe VBA will help, but I'm not VBA hero

[skipper.c]

Conditional Formatting works with defined range(s) and Boolean values: TRUE or FALSE so you can't define range by formula inside CF

Maybe VBA will help, but I'm not VBA hero

OK no worries, Thanks

[sandy666]

Welcome