Conditional formatting on entire sheet via Makro / VBA

[Sumo44]

Hi all,

I created userform and am using it as a sort of toolbar. Working fine, so no help needed on this.

I would like to have a macro execute when I press "BT2" which is button 2.

remembering we keep all our records in rows, ie from b2 to b66 or sometimes further so the end of range would have to be determined. Row 1 (A1:Axx) is a heading row.

What the macro needs to do is look at the entire range of data and look for cells colored in PINK, yes Pink.. lol.. only because it is soooo ugly
When it finds a pink cell in a row it needs to change the first cell to pink as well and move on.

ie...

Cell d5 is pink, so it needs to change d1 to pink.... cell f9 is pink, thus f1 has to be made pink.... h12 is pink, yup, correct, h1 has to go pink...

At the same time...
cell j1 is pink but there are no more pinks in that row ie j2:jxxx thus, j1 needs to go to no fill.

Any ideas for me please?

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Sumo44]

hahaha, made me smile, Thanks John.

Let me explain.. I use the code, and click and... Nothing.. check it and again and.. Nothing... Ohh hold.. the Coloum headings have changed clour, lol.. took me a while to spot it, hence the smiling. I need the rows to change colour, ie if a pink is found in row 5, then A5 needs to change, if found in lets say row 8, then A8 needs to change thus always the first cell in a rown where a pink is found.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Sumo44]

Ok, changed the code to this

Please Login or Register  to view this content.

And it works great BUT....

I made D5 pink, G5 pink and h3 Pink, applied the code, and D1, G1 and H1 went Pink. so that part of the logic is working.

Then I changed only H3 to no fill and ran the code again and it goes crazy.. scrolls down to CS541695 and hangs the application. Any suggestions ?

[Sumo44]

Ahh.. I see a diff between yours and mine

Please Login or Register  to view this content.

let me try it. hold on a sec....

[Sumo44]

BRILLIANT, just shows my skills are only at the beginning, your works. Thank you for that.

Now we only need the second part, ie if no pinks are found in a row (excluding in first cell) then the first cell needs to change to no fill...

ie D5 used to be Pink, ran the code and D1 was made Pink. Just as it is supposed to. now we change the fill of D5 to transparent and run the code again, it should now see that nothing in D is pink (remembering not to test in D1) and because nothing is Pink, D1 becomes transparent too. ??