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.
