**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.*