Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a conditional format in an excel spreadsheet that fills in the row is a cell has a certain value (or in this case text). The next column has the same formula but for a different colour. i.e. if B3 says x, change row to green. if C3 says x, change the row to blue etc.. the format is working fine until I need to clear the contents of a cell, when that happens it leaves the entire row (but not the cell) the colour it was under the format. i.e B3 says x so row turns green, then C3 is x so row turns blue. I then delete the x from C3. C3 becomes clear again but the rest of the row remains blue. How can I change this?
Solved by G. B. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 03/08/2018 - 10:53
Hello
Excelchat Expert 03/08/2018 - 10:53
How are you today?
User 03/08/2018 - 10:54
all good thanks - just confused:)
Excelchat Expert 03/08/2018 - 10:54
Ok, I will try my level best ti help you.
Excelchat Expert 03/08/2018 - 10:54
Is it possible for you to share the file?
Excelchat Expert 03/08/2018 - 10:55
so that I can relate to the description you have mentioned.
User 03/08/2018 - 10:55
formula is currently only in F3 and G3 but you'll see the issue
[Uploaded an Excel file]
Excelchat Expert 03/08/2018 - 10:57
So now if I put x in F3
Excelchat Expert 03/08/2018 - 10:57
it turns Blue
Excelchat Expert 03/08/2018 - 10:58
if I put x in G3 it turns green
User 03/08/2018 - 10:59
Yes, but of you then delete G3 it will stay green (the row but not the cell), I need it to either revert back to blue (if there is an x in F3) or go clear again
Excelchat Expert 03/08/2018 - 10:59
at my end
Excelchat Expert 03/08/2018 - 11:00
when I am putting both as X
Excelchat Expert 03/08/2018 - 11:00
then it is blue
Excelchat Expert 03/08/2018 - 11:00
when I am deleting G3 it remaining blue
Excelchat Expert 03/08/2018 - 11:00
when I am just deleting F3 and G3 is still as x it is green.
User 03/08/2018 - 11:01
arghhhh that's not what it's supposed to do!!!
Excelchat Expert 03/08/2018 - 11:02
then please tell me what exactly you want it to do.
User 03/08/2018 - 11:02
Right, what I need is if F3 has an x the row turns blue and then if an x goes in G3 the row goes green but, if I then delete G3 I need it to revert to blue
Excelchat Expert 03/08/2018 - 11:03
1. F3 - x - blue - working
Excelchat Expert 03/08/2018 - 11:03
2. F3 and G3 - x - Green - not working.
Excelchat Expert 03/08/2018 - 11:04
3. Delete G3 - TURN TO BLUE AGAIN - Working.
Excelchat Expert 03/08/2018 - 11:04
So I will make the 2nd point working.
Excelchat Expert 03/08/2018 - 11:04
Give me few minutes
User 03/08/2018 - 11:04
not quite because if you delete the content of F3 the rest of the row remains blue, why does it not revert back to clear?
Excelchat Expert 03/08/2018 - 11:04
It is
Excelchat Expert 03/08/2018 - 11:05
it is working as you have mentioned.
User 03/08/2018 - 11:06
weird, not at my end until I close the document and open it again.
Excelchat Expert 03/08/2018 - 11:06
Ok.
Excelchat Expert 03/08/2018 - 11:06
so it's working now?
Excelchat Expert 03/08/2018 - 11:06
after you have reopened the file.
User 03/08/2018 - 11:07
yes but only then
Excelchat Expert 03/08/2018 - 11:07
and again it stops working like that.
User 03/08/2018 - 11:08
yes, it will only show the correct colours after I have saved, closed and reopened
Excelchat Expert 03/08/2018 - 11:08
Please click Formula on the top - Calculation Option -
Excelchat Expert 03/08/2018 - 11:08
make sure - Automatic is selected.
User 03/08/2018 - 11:08
It is
Excelchat Expert 03/08/2018 - 11:08
Ok
Excelchat Expert 03/08/2018 - 11:09
That's weird.
User 03/08/2018 - 11:09
hahahaha I know!
Excelchat Expert 03/08/2018 - 11:09
Ok, let's let me send you my file
Excelchat Expert 03/08/2018 - 11:09
and then you try
User 03/08/2018 - 11:09
Part of the problem may be that we're using very old versions of Microsoft?
Excelchat Expert 03/08/2018 - 11:09
what is the version of your Excel?
User 03/08/2018 - 11:10
I think it's 7 - I work for a charity so small to non existant budget
Excelchat Expert 03/08/2018 - 11:10
Let me send my file
Excelchat Expert 03/08/2018 - 11:14
Try this
[Uploaded an Excel file]
Excelchat Expert 03/08/2018 - 11:15
I hope this should work file.
Excelchat Expert 03/08/2018 - 11:15
I deleted the old formatting and put the new conditional formatting.
User 03/08/2018 - 11:15
I cant open it!
User 03/08/2018 - 11:15
Can you tell me what you changed the formula too?
Excelchat Expert 03/08/2018 - 11:16
Ok
Excelchat Expert 03/08/2018 - 11:16
et's do it in your sheet then.
Excelchat Expert 03/08/2018 - 11:16
please click on Conditional Formatting
Excelchat Expert 03/08/2018 - 11:16
Manage Rules
User 03/08/2018 - 11:17
done and done:)
Excelchat Expert 03/08/2018 - 11:17
Ok
Excelchat Expert 03/08/2018 - 11:18
Now select this worsheet from the dropdown
Excelchat Expert 03/08/2018 - 11:18
You will see few rules.
Excelchat Expert 03/08/2018 - 11:18
Can you see
User 03/08/2018 - 11:19
Yes
Excelchat Expert 03/08/2018 - 11:19
uncheck all the rules
Excelchat Expert 03/08/2018 - 11:19
You will see check boxes under Stop if True
Excelchat Expert 03/08/2018 - 11:19
uncheck that and press apply
Excelchat Expert 03/08/2018 - 11:20
wait
Excelchat Expert 03/08/2018 - 11:20
sorry
Excelchat Expert 03/08/2018 - 11:20
Select the green colour one
Excelchat Expert 03/08/2018 - 11:20
and delete it
Excelchat Expert 03/08/2018 - 11:20
and do the same with blue color one.
Excelchat Expert 03/08/2018 - 11:20
then click apply
Excelchat Expert 03/08/2018 - 11:20
and press Ok.
User 03/08/2018 - 11:21
all done
Excelchat Expert 03/08/2018 - 11:21
so now put an x and no formatting is coming
Excelchat Expert 03/08/2018 - 11:21
Kindly confirm
User 03/08/2018 - 11:22
YEs
Excelchat Expert 03/08/2018 - 11:22
Ok
Excelchat Expert 03/08/2018 - 11:22
Now select cells from A3 to F3.
Excelchat Expert 03/08/2018 - 11:23
and Click conditional formatting
Excelchat Expert 03/08/2018 - 11:23
- New rule
Excelchat Expert 03/08/2018 - 11:23
Select - use a formula to determine which cells to format
Excelchat Expert 03/08/2018 - 11:24
paste this in the box below that
Excelchat Expert 03/08/2018 - 11:24
=$F3="x"
Excelchat Expert 03/08/2018 - 11:24
then click on Format - Fill - select the color (blue) - Click Ok
Excelchat Expert 03/08/2018 - 11:25
and Apply
Excelchat Expert 03/08/2018 - 11:25
let me know when you do this.
User 03/08/2018 - 11:25
all done
Excelchat Expert 03/08/2018 - 11:25
now put a x in F3
Excelchat Expert 03/08/2018 - 11:25
did it turn blue?
User 03/08/2018 - 11:25
F3 did but not the row.
User 03/08/2018 - 11:26
nope, now it's caught up with itself and the row is blue
Excelchat Expert 03/08/2018 - 11:26
Can you click on Conditional formatting - Manage rule and send me the screenshot
Excelchat Expert 03/08/2018 - 11:27
if it is just turning F3 blue then you did not select the cells from A3 to F3.
Excelchat Expert 03/08/2018 - 11:28
these cells should be selected before using conditional formatting.
User 03/08/2018 - 11:29
Now it caught up with itself it's all blue. I'm becoming more and more convinced that I had the correct formula it's just too much for our version of excel to be able to keep up with at the pace I wanted it to.
Excelchat Expert 03/08/2018 - 11:29
it should work fine in 2008
Excelchat Expert 03/08/2018 - 11:29
2007
Excelchat Expert 03/08/2018 - 11:29
even it will work in 2003
User 03/08/2018 - 11:30
so it is working, just not immediately..
Excelchat Expert 03/08/2018 - 11:31
Can you please send me the screen shot - Conditional formatting - Manage rule
Excelchat Expert 03/08/2018 - 11:32
Please extend the session

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc