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!)
Simple Condition Format Question
-
- StarLounger
- Posts: 61
- Joined: 29 Jul 2010, 14:52
Simple Condition Format Question
I don't have one
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Simple Condition Format Question
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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
-
- StarLounger
- Posts: 61
- Joined: 29 Jul 2010, 14:52
Re: Simple Condition Format Question
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 :)
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 :)
I don't have one
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Simple Condition Format Question
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.)
=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.)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 61
- Joined: 29 Jul 2010, 14:52
Re: Simple Condition Format Question
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
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
I don't have one