MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I have a table in Excel which is the result of an SQL query (which has a parameter that can change)
E.g.
Columns A, B, C, D
Sorted on Column D.
I would like to colour in each row based on the value in Column A.
[b]A B C D [/b]
c x x 1
d y y 1
c z z 2
e a a 2
So, rows 1 & 3 to have the same colour, and rows 2 & 4 to have their own colours.
I could do it with a bunch of conditional formatting rules, but it would have to be redone each time the table was refreshed.
Anybody got any bright ideas?
probably solvable with NAMED RANGES.
ALthough why not just conditional formatting? Have you got loads of options in A?
Yes, loads of potential values in A.
Will conditional formatting stick around if I refresh the table? I wouldn't mind (too much) doing it once if it stuck...
yes, conditional formatting is a format characteristic, so assuming the sql population is data only (or referenced) it shouldnt change any formats
Cheers. Job for Monday 🙂
Either that or vba to create a function for the sheet. It was beyond my abilities. Might have an example at work but can't promise
Be aware that there is an upper limit to the number of formats you can apply (changed with version of Excel too, to make it hard to predict!)
When they "improved" the conditional formatting function a few generations ago, they made it harder to manage, IMO. It always seems to create a gazillion separate rules to replace the one you create for a range. And it can go really screwy if you sort or move things around.
I'd favour a VBA approach here.