Hi guys,

I'm afraid i'm very much an excel novice so please go easy on me

I'im trying to create a conditional format on my spreadsheet, but 1 part is taking forever and being very tedious, hoping for your help

on sheet 2 i have various cells of data and various cells empty

on sheet 1 im trying to conditional format according to those cells on sheet 2

for example

$a$1:$C$1 | =ISBLANK('sheet 2'!$A$1) | cell fill grey
$a$2:$C$2 | =ISBLANK('sheet 2'!$A$2) | cell fill grey
$a$3:$C$3 | =ISBLANK('sheet 2'!$A$3) | cell fill grey
$a$4:$C$4 | =ISBLANK('sheet 2'!$A$4) | cell fill grey
$a$5:$C$5 | =ISBLANK('sheet 2'!$A$5) | cell fill grey

I want to copy this to the next column but accordingly

eg

$D$1:$F$1 | =ISBLANK('sheet 2'!$B$1) | cell fill grey
$D$2:$D$2 | =ISBLANK('sheet 2'!$B$2) | cell fill grey
$D$3:$D$3 | =ISBLANK('sheet 2'!$B$3) | cell fill grey
$D$4:$D$4 | =ISBLANK('sheet 2'!$B$4) | cell fill grey
$D$4:$D$5 | =ISBLANK('sheet 2'!$B$5) | cell fill grey

If I use format painter, it copies the cell to the next requested columns but the conditional format leaves the rule as was previous

eg

$D$1:$F$1 | =ISBLANK('sheet 2'!$A$1) | cell fill grey
$D$2:$D$2 | =ISBLANK('sheet 2'!$A$2) | cell fill grey
$D$3:$D$3 | =ISBLANK('sheet 2'!$A$3) | cell fill grey
$D$4:$D$4 | =ISBLANK('sheet 2'!$A$4) | cell fill grey
$D$4:$D$5 | =ISBLANK('sheet 2'!$A$5) | cell fill grey

so I have to manually edit the rule so all the A's become B's

If I try highlighting, copy and special format paste, it does the same as above so have to manually edit again (also this very often freezes excel until I do a forced end task)

if I highlight, then try extending, it just extends the cells for sheet 1 and does not adjust the formula according to sheet 2

eg

$a$1:$C$1 | =ISBLANK('sheet 2'!$A$1) | cell fill grey | becomes | $A$1:$F$1 | =ISBLANK('sheet 2'!$A$1) | cell fill grey
$a$2:$C$2 | =ISBLANK('sheet 2'!$A$2) | cell fill grey | becomes | $A$1:$F$1 | =ISBLANK('sheet 2'!$A$2) | cell fill grey
$a$3:$C$3 | =ISBLANK('sheet 2'!$A$3) | cell fill grey | becomes | $A$1:$F$1 | =ISBLANK('sheet 2'!$A$3) | cell fill grey
$a$4:$C$4 | =ISBLANK('sheet 2'!$A$4) | cell fill grey | becomes | $A$1:$F$1 | =ISBLANK('sheet 2'!$A$4) | cell fill grey
$a$5:$C$5 | =ISBLANK('sheet 2'!$A$5) | cell fill grey | becomes | $A$1:$F$1 | =ISBLANK('sheet 2'!$A$5) | cell fill grey

Hoping you get the idea lol

I've got 30+ rows on my sheet and each of the 3 column cells represent a day in the year so as you can imaging, my columns go upto APA

thats a lot to get through and taking much time to deal with working individually through

I've tried searching google for a faster way and even tried looking for a way to find a replace in conditional formatting rules but there seems to not be a way to do that.

Please can you tell me if there is a faster way to process this

Thankyou VERY! much in advance

Please remember, I am an excel novice