Question description:
This user has given permission to use the problem statement for this blog.
Hi I am looking for help on conditional formatting I have two ranges of 11 cells and I would like a singular cell in the first range to flash red if it less than the bottom range, could you please assist?
Solved by D. L. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User
21/08/2017 - 12:19
so first range is G9:G21
User
21/08/2017 - 12:19
and the second is G32:44
Excelchat Expert
21/08/2017 - 12:20
Okay,
Excelchat Expert
21/08/2017 - 12:20
When you say if it is less than,
User
21/08/2017 - 12:20
so like if G32 is less than G9, i need it to flash red
Excelchat Expert
21/08/2017 - 12:20
do you mean the sum of all the numbers or just that one number compared to the others?
User
21/08/2017 - 12:20
the one number
User
21/08/2017 - 12:20
so like if G33 is less than G10
User
21/08/2017 - 12:21
i need G10 to flash red
User
21/08/2017 - 12:21
and i need this repeated for the whole range
User
21/08/2017 - 12:22
cause as of now, it highlights the entire range
User
21/08/2017 - 12:23
so just need it to highlight the one cell that is too large
Excelchat Expert
21/08/2017 - 12:23
https://docs.google.com/spreadsheets/d/1f6UlO3EshEdGd21QRyFWTq0Hr2iMLqouICrtgQd3IXM/edit?usp=sharing
Excelchat Expert
21/08/2017 - 12:23
Please click on on the link.
User
21/08/2017 - 12:23
could you copy and paste it? sorry my company website doesnt let me use google docs
User
21/08/2017 - 12:24
or how do i edit this formula?
User
21/08/2017 - 12:24
=$G$32:$G$43<$G$9:$G$20
User
21/08/2017 - 12:24
cause thats what i have currently
Excelchat Expert
21/08/2017 - 12:25
I'm reviewing now. One moment please.
User
21/08/2017 - 12:25
ok ty
User
21/08/2017 - 12:29
any idea?
Excelchat Expert
21/08/2017 - 12:29
Yes.
Excelchat Expert
21/08/2017 - 12:29
Now we can continue into the explanation step of this session. I am going to perform the following:
User
21/08/2017 - 12:29
ok
Excelchat Expert
21/08/2017 - 12:29
=$G9<$G32
Excelchat Expert
21/08/2017 - 12:29
Put that in custom formula within conditional formatting.
Excelchat Expert
21/08/2017 - 12:30
I’ve completed the solution, take a look if you would like. We can now continue to the discussion stage and I can answer any questions you may have with the solution I provided.
User
21/08/2017 - 12:30
how do you copy it to the rest though?
User
21/08/2017 - 12:30
is there an easy way?
Excelchat Expert
21/08/2017 - 12:30
Once you have that in G9 you copy the rest through by using the format painter at the top of excel.
User
21/08/2017 - 12:30
oh ok
User
21/08/2017 - 12:30
one second gonna test
Excelchat Expert
21/08/2017 - 12:30
Okay, thank you.
User
21/08/2017 - 12:32
doesnt seem to be working//
Excelchat Expert
21/08/2017 - 12:33
look at G9
User
21/08/2017 - 12:33
yeah im at G9
User
21/08/2017 - 12:33
so if G9>G32
User
21/08/2017 - 12:33
i want it to flash red
Excelchat Expert
21/08/2017 - 12:33
Nothing happens.
Excelchat Expert
21/08/2017 - 12:34
I thought is was less than, not greater?
User
21/08/2017 - 12:34
no its greater
Excelchat Expert
21/08/2017 - 12:34
=$G10<$G33
Excelchat Expert
21/08/2017 - 12:34
You use this formula then.
Excelchat Expert
21/08/2017 - 12:35
Sorry for the confusion.
User
21/08/2017 - 12:35
ok i think i got it
User
21/08/2017 - 12:35
how do you use the format painter to quickly copy it?
User
21/08/2017 - 12:35
i also need to add it to the other columns as well
Excelchat Expert
21/08/2017 - 12:36
So go to G9, press the format painter icon and drag to the end of your range.
Excelchat Expert
21/08/2017 - 12:36
Take the $ out of your formula and then you can drag it whereever.
Excelchat Expert
21/08/2017 - 12:36
so =G10<G33
Excelchat Expert
21/08/2017 - 12:36
instead of what I sent before.
User
21/08/2017 - 12:37
and i can drag horizontally as well?
User
21/08/2017 - 12:37
like into other columns?
Excelchat Expert
21/08/2017 - 12:37
yup.
User
21/08/2017 - 12:37
ok awesome it works
User
21/08/2017 - 12:37
thanks for your help
Excelchat Expert
21/08/2017 - 12:38
Great! :)
Excelchat Expert
21/08/2017 - 12:38
You're very welcome is was my pleasure.
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.
