**Question description:**

*This user has given permission to use the problem statement for this blog.*

I want to be able to alter the appearance (font color and fill) of a column of numbers using conditional formatting, based on being greater than or less than the numbers from another column. I am using Excel 2016. I am unable to get any formula to work. Please help!

Solved by I. J. in 59 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
21/08/2018 - 11:43

Hi welcome!

User
21/08/2018 - 11:48

Hello! This is the first time I have seen your website. Did you see my question?

Excelchat Expert
21/08/2018 - 11:48

Yes

Excelchat Expert
21/08/2018 - 11:48

You can upload a sheet if need

Excelchat Expert
21/08/2018 - 11:49

or we can use this example, however this view screen in not EXCEL 2016 and some things are different'

Excelchat Expert
21/08/2018 - 11:50

I take it you want column A, highlighted if between the other 2 numbers?

Excelchat Expert
21/08/2018 - 11:52

Hello?

User
21/08/2018 - 11:52

Hold on a sec and I will upload a file

User
21/08/2018 - 11:54

Here is the file

[Uploaded an Excel file]

Excelchat Expert
21/08/2018 - 11:55

OK, open

Excelchat Expert
21/08/2018 - 11:56

What is the condition to format?

User
21/08/2018 - 11:56

Based on column A I want to have columns B and C change color depending whether the entries are greater than column A or less.

User
21/08/2018 - 11:56

Hopefully text color and fill color.

Excelchat Expert
21/08/2018 - 11:57

They are all greater or less, since none match exactly

User
21/08/2018 - 11:57

yes

Excelchat Expert
21/08/2018 - 11:58

You mean a different color if greater and another color if less?

User
21/08/2018 - 11:59

I have read several statements on the web that once you get one entry correct you can use Format Painter to change the others. That is not working at all on my spreadsheet.

User
21/08/2018 - 11:59

Yes

Excelchat Expert
21/08/2018 - 11:59

Format painter?

Excelchat Expert
21/08/2018 - 11:59

NO

Excelchat Expert
21/08/2018 - 11:59

You can apply a conditional format in 2016

Excelchat Expert
22/08/2018 - 12:00

based on a Formula, you can change the numbers or text in a cell a different color

Excelchat Expert
22/08/2018 - 12:00

So I think you want this..

User
22/08/2018 - 12:01

Yes, but I am having difficulty getting it to work.

Excelchat Expert
22/08/2018 - 12:01

If either column B or C is > than column A, use GREEN

Excelchat Expert
22/08/2018 - 12:02

If either column B or C is < than column A, use RED

Excelchat Expert
22/08/2018 - 12:02

is that the correct problem statement for this sample data?

User
22/08/2018 - 12:03

The conditional formatting in Excel is not very user friendly, in my estimation. Can you post how the formula should look? There seem to me to be different ways to enter it, and I am not getting it right.

Excelchat Expert
22/08/2018 - 12:04

I could if I knew waht to code how

Excelchat Expert
22/08/2018 - 12:04

I will set up this set and review with you

User
22/08/2018 - 12:04

ok

Excelchat Expert
22/08/2018 - 12:05

we can use this viewer, but it is not the same as EXCEL

Excelchat Expert
22/08/2018 - 12:05

Please stand by and I will get you a file

User
22/08/2018 - 12:05

OK. Thank you.

User
22/08/2018 - 12:08

One other thing I need it to do is that when the B and C entries change, that the appearance changes to reflect.the new amouont

Excelchat Expert
22/08/2018 - 12:12

[Uploaded an Excel file]

Excelchat Expert
22/08/2018 - 12:12

Ok, we need to review

Excelchat Expert
22/08/2018 - 12:13

Please open and then select CONDITIONAL FORMATING from the main menu

Excelchat Expert
22/08/2018 - 12:13

Then select MANAGE RULES

Excelchat Expert
22/08/2018 - 12:14

Sorry, but first put the cursor ion either column B or C, to see the RULES set up for each

Excelchat Expert
22/08/2018 - 12:16

Hello?

User
22/08/2018 - 12:16

I don't seem to be able to open the file. When I try, the screen says "something went wrong.

Excelchat Expert
22/08/2018 - 12:16

let me try to save in a lower version

User
22/08/2018 - 12:17

When I try to open it, it opens a window in Excelchat.

Excelchat Expert
22/08/2018 - 12:17

And then an error msg?

Excelchat Expert
22/08/2018 - 12:17

[Uploaded an Excel file]

Excelchat Expert
22/08/2018 - 12:18

Try a lower version, if not I can walk you thru

User
22/08/2018 - 12:18

No, it locks up and the only thing I can do is close that window

Excelchat Expert
22/08/2018 - 12:18

ok, we have been having file share problems lately

User
22/08/2018 - 12:18

I use Excel 2016

Excelchat Expert
22/08/2018 - 12:18

Please dont take ti out on me

User
22/08/2018 - 12:18

Can you email It to me?

Excelchat Expert
22/08/2018 - 12:18

I can but only via site support

Excelchat Expert
22/08/2018 - 12:19

Lets walk you thru

User
22/08/2018 - 12:19

Ok

Excelchat Expert
22/08/2018 - 12:19

Open the sheet you sent me and -goto to B1

Excelchat Expert
22/08/2018 - 12:19

Then select CONDITIONAL FORMATTING ,

Excelchat Expert
22/08/2018 - 12:20

Then MANAGE RULEs

Excelchat Expert
22/08/2018 - 12:20

with me so far?

User
22/08/2018 - 12:20

Yes

Excelchat Expert
22/08/2018 - 12:20

then NEW RULE

Excelchat Expert
22/08/2018 - 12:21

That brings up a menu

User
22/08/2018 - 12:21

Ok

Excelchat Expert
22/08/2018 - 12:21

Select USE A FORMULA

User
22/08/2018 - 12:21

ok

Excelchat Expert
22/08/2018 - 12:22

IN the box FORMAT where formula is TRUE

Excelchat Expert
22/08/2018 - 12:22

enter =B1<A1

Excelchat Expert
22/08/2018 - 12:22

Then select FORMAT

User
22/08/2018 - 12:22

ok so far

Excelchat Expert
22/08/2018 - 12:23

On the FORMAT select the COLOR box, use RED here for less than

User
22/08/2018 - 12:24

That is for the font

Excelchat Expert
22/08/2018 - 12:24

YES

Excelchat Expert
22/08/2018 - 12:24

you can experiment with that-FILL, shading , other,

User
22/08/2018 - 12:24

I can also choose the fill from here.

Excelchat Expert
22/08/2018 - 12:25

Yes, but be careful, if you FILL with a real dark color like RED, you wont see the numbers

User
22/08/2018 - 12:25

Yes

Excelchat Expert
22/08/2018 - 12:26

The last step is important- APPLIES TO:

Excelchat Expert
22/08/2018 - 12:26

This needs o cover the range you want for this CONDITION

Excelchat Expert
22/08/2018 - 12:26

in this case APPLIES TO: =$B$1:$B$7

User
22/08/2018 - 12:26

I don't see "Applies to"

Excelchat Expert
22/08/2018 - 12:27

hit OK, OK, to clear the color set screens

User
22/08/2018 - 12:28

Ok.. I found it.

Excelchat Expert
22/08/2018 - 12:28

APPLIES TO: =$B$1:$B$7

Excelchat Expert
22/08/2018 - 12:28

now column B should be all in RED numbers

User
22/08/2018 - 12:29

Now... what if I want it to revert back when the entry in column A drops below what is in B?

User
22/08/2018 - 12:29

Yes all are in Red.

Excelchat Expert
22/08/2018 - 12:29

Change A and you should see

Excelchat Expert
22/08/2018 - 12:29

Change A1 to 25

User
22/08/2018 - 12:30

aha! It changed to green

Excelchat Expert
22/08/2018 - 12:30

Green?

User
22/08/2018 - 12:30

Yes

Excelchat Expert
22/08/2018 - 12:30

OK, if you added a 2nd rule for GREEN -good

Excelchat Expert
22/08/2018 - 12:31

YOu can add multiple rules for each column

User
22/08/2018 - 12:31

Ok... I think you have put me on the right track!

Excelchat Expert
22/08/2018 - 12:31

Great!

Excelchat Expert
22/08/2018 - 12:31

Sorry the file thing crashed, but I think we would have needed the walk thru anyway

User
22/08/2018 - 12:32

Thank you so much. I appreciate your time.

Excelchat Expert
22/08/2018 - 12:32

Ok, bye now

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