We help IT Professionals succeed at work.

Excel conditional formatting.

137 Views
Last Modified: 2017-04-03
All,

I need a conditional format formula that will look in cell B2 from C2 if the value is greater arrow up will appear in C2 if lower then arrow down will appear and I need to copy the formula across the row. So it will do the same for D2 and C2, E2 and D2..... etc....

Thank in advance!
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Hi Edward,

Conditional Formatting with Icon Set rule, you cannot use Formula with cell reference as range. With the help of Helper Column we can create Icon Set. See below:
Icon SetFormula for Helper Column would be:
=SIGN(B2-C2)

Open in new window

To determine which cell is greater, lower or equal.
Hope this helps.
Edward-Icon-Set-Conditional-Formatt.xlsx
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
I need the arrows in the cell not in another column... please.
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
I am looking at this more closely, so let me clarify this a bit more. If B2 = 100 and C2 = 99, I need to have C2 with the value 99 and a down arrow and the same for all the following cells. If each cell is higher or lower I need the value and the arrow in the same cell up arrow for higher value, down arrow for lower value.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Hi Edward,

As I said, we cannot refer to range, you can have Icon Set on the Cell, but you need to do Conditional Formatting to each cell like below:
Icon SetEdward-Icon-Set-Conditional-Formatt.xlsx
RaminTechnical Advisor
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
Ramin, I like that but can you make the arrows green and red? :)
RaminTechnical Advisor
CERTIFIED EXPERT

Commented:
Okay, but give me some more time.
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
OK.. so the only problem is I have one row of values I do not want to have another row with values and arrows. I want them all on the same row.
RaminTechnical Advisor
CERTIFIED EXPERT

Commented:
Shums will continue from here. I just wanted to give him an idea. He is really perfect in excel.
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
RaminTechnical Advisor
CERTIFIED EXPERT

Commented:
Well done !
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
Thanks gentlemen!
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're Welcome Edward! Glad eventually it worked for you :)
RaminTechnical Advisor
CERTIFIED EXPERT

Commented:
Thanks for that, Edward - much appreciated!