Simple Condition Format Question

[kpark91]

Hello,

How can I fill cells red, which are referring to blank cells?

So if A1 is blank
and in B1 I have = A1 (in B1, it's going to appear 0)

How do I fill B1 as red? (there might be 0 value inputted in A1 tho!)

[HansV]

Select B1.

Excel 2003 or before:
- Select Format | Conditional Formatting...
- Select Formula Is from the first dropdown.
- Enter the formula =ISBLANK(A1) in the box next to it.
- Click Format...
- Activate the Pattern tab.
- Select red.
- OK your way out.

Excel 2007 or later:
- Click Conditional Formatting in the Home tab of the ribbon.
- Select New Rule...
- Click 'Use a formula to determine which cells to format'.
- Enter the formula =ISBLANK(A1) in the 'Format values where this formula is true' box.
- Click Format...
- Activate the Fill tab.
- Select red.
- OK your way out.

[rory]

Also posted here.

[kpark91]

Thank you.

Sorry about the cross-post HansV!
I really didn't mean anything by it at all.
I didn't realize it was something behind the back.
I will post the link if I cross-post again.


Anyways, by the way, your solution worked like a charm in my test sheet.

However, this is the real problem.

I have two worksheets (Sheet1 and Sheet2)

Sheet2 cells are purely referring to Sheet1 containing many values (including 0)
Sheet2 refers to Sheet1 cells correctly but when cell in Sheet1 is blank,
in Sheet2 it says 0 instead of a blank cell.


I want to indicate on Sheet2 that the cell it's referring to in Sheet1 is blank

Would this be possible?

Thank you :)

[HansV]

Instead of a formula such as

=Sheet1!A1

you could use

=IF(Sheet1!A1="","",Sheet1!A1)

(It was rory who pointed out that you had also posted the same question elsewhere.)

[kpark91]

Oh. Sorry about it rorya :)

Actually, the first answer you posted was right too
But the second one too was right :)

In my conditional formatting,
I just had to reference the whole worksheet and put
=ISBLANK(cell's formula)

then it worked beautifully :)

PS: I never realized formula 'copy down/left/right/up' itself in conditional formatting too!

Thanks for the help