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.
