All solutions CONDITIONAL FORMATTINGGreater Than Expert Solution – How to Use Conditional Formatting to Highlight Values Greater Than or Equal To

Excel - How to Use Conditional Formatting to Highlight Values Greater Than or Equal To - Expert Solution

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.

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