Conditional Formatting copying

[JaSonic75]

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

[Sadath31]

HI

Select Range in sheet1 and ConditionalFormating>NewRule>Use A Formula to....


=ISBLANK(OFFSET(Sheet2!$A1,0,(INT((COLUMN()+2)/3)-1)))
use the formula and format as required...

[JaSonic75]

Many thanks for your reply, not sure what I done wrong but couldn't get that formula to work...

Hopefully I've input the screenshots correctly

but as you can see, I've tediously made it up to the date of 07/08/2017 so need to commence from cell LH5:LJ5 which on sheet2 corresponds to DD4

I've edited that formula I was hoping accordingly:

=ISBLANK(OFFSET('Daily Routing'!$DD4,0,(INT((COLUMN()+2)/3)-1)))

All the cells turned grey as hoped (LH5:APA33), but on sheet 2 I have 1 cell (DD32), that has data which theoretically should have remained white in cells LH33:LJ33, but this still greyed out

Sorry, please where did I go wrong?

Thankyou ever so much

Attachment 525807

Attachment 525808

[NC Training Services]

the format painter wont copy down because of the $'s - you need to remove the $'s before the Row numbers. keep the column $'s just remove the row ones, then format painter will work.

[FDibbins]

2 suggestions on what you are already doing...

Please Login or Register  to view this content.

$a1:$C5 | ='sheet 2'!$A$1="" | cell fill grey

This should copy cover OK, but if not, just make the adjustment in the 1st cell of each range, it will get applied to the whole range