vba - Conditional formatting on entire sheet via Macro -

need help. in spreadsheet there macro when activated test see if cell color in entire set of data pink. if 1 found fist cell marked pink, alerting me fact on specific row there pink cell somewhere.

once have paid attention pink cell, mark transparent again , move on next one.

what need add working code below test see if there no pinks .. ie..

if cell a9 pink means somewhere in row 9 there pink. need check if still there, if not make a9 transparent.

the code below 1 marks a9 pink (as per example above.

code:

sub pink() dim rcell range each rcell in range(cells(1, 1), cells(1, activesheet.usedrange.columns.count))     if rcell.interior.colorindex = 38         cells(activesheet.usedrange.rows.count, rcell.column).select             until activecell.interior.colorindex = rcell.interior.colorindex                 activecell.offset(-1).select             loop             if activecell.row = rcell.row rcell.interior.colorindex = xlnone     end if next rcell each rcell in activesheet.usedrange     if rcell.interior.colorindex = 38         cells(rcell.row, 1).interior.colorindex = 38     end if next rcell end sub 

to run through scenario:

row 1 headings , excluded, column column things marked pink..

b8 pink when macro runs, a8 goes pink, d14 pink , a14 goes pink, a18 pink there no more pinks in row 18 a18 goes transparent etc etc..

any suggestions ?

you can run below transparent procedure. make cells in column a transparent if there no pink cells in row respectively.

sub transparent()      dim lastrow long, rcell range, blnred boolean     lastrow = activesheet.usedrange.rows.count      = 1 lastrow         if cells(i, 1).interior.colorindex = 38             each rcell in range(cells(i, 2), cells(i, activesheet.usedrange.columns.count))                 if rcell.interior.colorindex = 38                     blnred = true                     exit                 else                     blnred = false                 end if             next              if blnred = false                 cells(i, 1).interior.pattern = xlnone             end if         end if     next end sub