Excel conditional formatting.

Edward Pamias
Edward Pamias used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ShumsManaging Director/Excel VBA Developer
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ShumsManaging Director/Excel VBA Developer
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
Commented:
if you read the data from another cell(s) it can be possible.
Edward-Icon-Set-Conditional-Formatt.xlsx
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

Commented:
Okay, but give me some more time.
ShumsManaging Director/Excel VBA Developer
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

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
Distinguished Expert 2018
Commented:
Thanks Ramin :) Its very late here.

Edward,

Please see below:
Conditional FormattingHope this would be last change. :)
Edward-Icon-Set-Conditional-Formatt.xlsx
RaminTechnical Advisor

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

Author

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

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

Commented:
Thanks for that, Edward - much appreciated!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial