Formula for highlighting number that decrease cell by cell and percentage difference of each cell

E

excelrater

New Member
Joined
May 17, 2019
Messages
21
This is a little hard to explain but I have two things i would like to achieveI am an extreme newbie so any help even written formulas for me to try would be wonderful

1. What I would like to achieve is starting from cell A2 and working to the right of the cell---> to the next cell B2 if the number is higher in A2 then B2 I want it A2 Highlighted by it being highlighted(A2) it means it is larger then B2. Then go on to the next cell B2 and compare it to the cell on the right C2 if B2 is Higher then C2 then I would like B2 highlighted. And the formula going all the way to J2. so A2 see if it is higher then B2 if it is A2 will be highlighted, then the same for B2 see if it is higher then C2 if it is B2 will be highlighted,then the same for C2 see if it is higher then D2 if it is C2 will be highlighted, then the same for D2 see if it is higher then E2 if it is D2 will be highlighted ALL THE WAY TILL J2. I only want the number that is higher to the next cell to be highlighted starting from left to right.

2.and if possible to show the percentage difference of how much more is A2 higher Then B2 etc.....

I will upload a blank sheet and a sheet where i would like the formula to highlight

any help will be much appreciated.
A2 B2 C2 D2 E2 F2 G2 H2 I2 J2
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 640"]
<tbody>[TR]
[TD="width: 64, align: right"]1. 1089521[/TD]
[TD="width: 64, align: right"]876191[/TD]
[TD="width: 64, align: right"]572320[/TD]
[TD="width: 64, align: right"]362712[/TD]
[TD="width: 64, align: right"]171594[/TD]
[TD="width: 64, align: right"]58012[/TD]
[TD="width: 64, align: right"]59249[/TD]
[TD="width: 64, align: right"]43508[/TD]
[TD="width: 64, align: right"]109115[/TD]
[TD="width: 64, align: right"]142496[/TD]
[/TR]
[TR]
[TD="align: right"]2. 1366944[/TD]
[TD="align: right"]308696[/TD]
[TD="align: right"]92939[/TD]
[TD="align: right"]202550[/TD]
[TD="align: right"]600641[/TD]
[TD="align: right"]180249[/TD]
[TD="align: right"]85376[/TD]
[TD="align: right"]935[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-5000[/TD]
[/TR]
[TR]
[TD="align: right"]3.15995969[/TD]
[TD="align: right"]7151714[/TD]
[TD="align: right"]4205283[/TD]
[TD="align: right"]-719504[/TD]
[TD="align: right"]3082535[/TD]
[TD="align: right"]-620273[/TD]
[TD="align: right"]2055152[/TD]
[TD="align: right"]225922[/TD]
[TD="align: right"]64325[/TD]
[TD="align: right"]-273826[/TD]
[/TR]
[TR]
[TD="align: right"]4. -257062[/TD]
[TD="align: right"]-707224[/TD]
[TD="align: right"]-109865[/TD]
[TD="align: right"]-138332[/TD]
[TD="align: right"]-140853[/TD]
[TD="align: right"]-52331[/TD]
[TD="align: right"]-76367[/TD]
[TD="align: right"]-38930[/TD]
[TD="align: right"]-2988[/TD]
[TD="align: right"]-26591[/TD]
[/TR]
[TR]
[TD="align: right"]5. 0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]6. 0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7.-1594985[/TD]
[TD="align: right"]-1519345[/TD]
[TD="align: right"]-887009[/TD]
[TD="align: right"]-926182[/TD]
[TD="align: right"]-527912[/TD]
[TD="align: right"]-238779[/TD]
[TD="align: right"]-234317[/TD]
[TD="align: right"]-199310[/TD]
[TD="align: right"]-2988[/TD]
[TD="align: right"]-26591[/TD]
[/TR]
[TR]
[TD="align: right"]8. 0[/TD]
[TD="align: right"]-152060[/TD]
[TD="align: right"]-137265[/TD]
[TD="align: right"]-851788[/TD]
[TD="align: right"]305106[/TD]
[TD="align: right"]13380[/TD]
[TD="align: right"]-11750[/TD]
[TD="align: right"]-5000[/TD]
[TD="align: right"]-22276[/TD]
[TD="align: right"]19461[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Re: Hello Need Help formula for highligting number that decrease cell by cell and percentage difference of each cell

here is a screeshot it does so
gj3BhCN
me of them properly but for example G2 is highlighted and it shouldnt be because g2 is less than h2
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Re: Hello Need Help formula for highligting number that decrease cell by cell and percentage difference of each cell

gj3BhCN
https://ibb.co/gj3BhCN here is the image after using the formula you made me. it does so
gj3BhCN
me of them properly but for example G2 is highlighted and it shouldnt be because g2 is less than h2
 
Upvote 0
Re: Hello Need Help formula for highligting number that decrease cell by cell and percentage difference of each cell

g2 should be checking cell block h2 and if g2 is higher then it should be highlighted but g2 is actually lower then h2 so it should not be highlighted. Hope you can see what i mean if we can tweak your fomula would be aweosme to make it work or did i do something wrong?
qqq-Capture.jpg
[/URL][/IMG]
 
Upvote 0
Re: Hello Need Help formula for highligting number that decrease cell by cell and percentage difference of each cell

my goal is to only highlight the cell to the left if it is higher then the cell to the right of it for example A2 is higher then B2 so A2 should be highlighted(i need it going from left to right a2 being the furthest to the left then check to right of it which is B2) . Then B2 if higher then C2 then B2 should be Highlighted then continue on the next cell it should be checking is C2 is higher then D2 if it is higher then C2 should be highlighted ETC..
another problem is for example the - numbers for example c12 when it checks with d12 c12(-102060) should be highlighted since it number is higher value then d12(-137265)

hope this helps at what I am trying to do, if not please let me know so i can explain better. :-)
 
Last edited:
Upvote 0
Re: Hello Need Help formula for highligting number that decrease cell by cell and percentage difference of each cell

bump please help
 
Upvote 0
Re: Hello Need Help formula for highligting number that decrease cell by cell and percentage difference of each cell

Your OP made it look like your data started in A2, but the pictures look like it starts in B2.

Try this. Do the exact same thing, but adjust the formula to be whatever the first 2 cells on the first row of your data are.

So, if your data starts in B2, then the formula would be, =AND(B2>C2,C2<>"")
 
Upvote 0
Re: Hello Need Help formula for highligting number that decrease cell by cell and percentage difference of each cell

lrobbo314 said:
Your OP made it look like your data started in A2, but the pictures look like it starts in B2.

Try this. Do the exact same thing, but adjust the formula to be whatever the first 2 cells on the first row of your data are.

So, if your data starts in B2, then the formula would be, =AND(B2>C2,C2<>"")
Click to expand...

lrobbo314 it worked thank you sooooooooooooooooooooooooo much for helping me thank you thank you thank you, is there way to make the percentage difference show in the cell that are higher?
 
Upvote 0
Re: Hello Need Help formula for highligting number that decrease cell by cell and percentage difference of each cell

Not sure exactly what you're looking for. The value has to be in the cell already, so displaying the percentage difference in the cell itself doesn't seem possible. Here is one possible solution. It's a VBA solution, so right click on the sheet tab, the area with the sheet name, and click 'View Code'. Then paste the code below. It gives the percentage difference in the status bar on the bottom of the screen.

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A2:J9")) Is Nothing Then 'change this range to where your data is
    Application.StatusBar = Format(Target.Value / Target.Offset(, 1).Value, "#.00%")
End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Cond. format empty cell based on column header and dates in other cells
Replies
1
Views
660
haruspication
H
V
  • Question Question
SEARCH BOX highlight only cell instead of the row
Replies
0
Views
379
Vov4a123
V
D
  • Question Question
Referring to an "Active Cell" in an INDEX MATCH formula?
Replies
4
Views
7K
dougmarkham
D
V
  • Question Question
Assigning sequence of numbers to visible cells only using VBA code (avoid hidden rows)
2
Replies
10
Views
2K
James006
James006
Z
  • Question Question
Conditional Formatting: Highlights Cells in Two Columns that are Equal to, or Between Reference Cells
Replies
5
Views
358
Eric W
Eric W

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top