flag or highlight when number entered exceeds a set value in relation to previous cell

tchelen

New Member
Joined
May 7, 2009
Messages
7
Okay, don't know if that Title will do it. However hopefully someone can help me with the following. I think it is conditional formatting that I want to use, but it doesn't seem to be quite right.
I have a row of numbers and I want to have the system notify me or colour the cell or in some way indicate when the number entered is below the previous cell's value by say 1,000,000. or perhaps a certain %, say 25%.
It is a double check on our data base numbers to ensure there are no system errors or large fluctuations that are not expected.
With conditional formatting I cannot see how it will allow me to compare the one cell with the previous and enter a value (1,000,000) or a % reduction.
Any suggestions? Please and Thank you in advance.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi, and welcome to the Board!

Let's say that you have data in columns A and B, with a reference value in D1.
Select B2 to the end of the data and start conditional formatting.
In 2003 and earlier change Value Is... to Formula Is...
In 2007 select New Rule and choose Formula (last item on the pick list).
In the formula bar, put the formula
=ABS(B2-A2)>=$D$1
SElect the background fill or pattern, click OK to get out of the dialog.

Denis
 

tchelen

New Member
Joined
May 7, 2009
Messages
7
Thanks Denis: I seem to have managed to apply it to one cell but not the whole column. Here is the formula I put in to adapt it to the column, but it still looks as if it is only applying to D2
=ABS(C2:C48-D2:D48)>=$B$2
(My columns are a little different than your examples. But basically what I am trying to do is if D is less than C by the amount or percentage in B than highlight red.)
How can I get it to apply to the whole column, with each cell referring to the cell to it's left. Is there a way to copy and paste a conditional format?
Or does my formula need adjusting?
Oh, I have the 2007 version.
Thanks for your reply. Helen.
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Helen, close...

Select all cells that you want to format in column D, then start the conditional formatting
Type the formula as though you were just interested in row 2.
ie:
=ABS(C2-D2)>=$B$2
The conditional format will apply to all selected cells.

Denis
 

tchelen

New Member
Joined
May 7, 2009
Messages
7
Wonderful! Thanks Denis, that worked great. You did say to select the range in your first message, but I missed that.
It looks like it all copies with Format Painter as well for when I add new columns.
Thanks again, Helen.

Hi Helen, close...

Select all cells that you want to format in column D, then start the conditional formatting
Type the formula as though you were just interested in row 2.
ie:
=ABS(C2-D2)>=$B$2
The conditional format will apply to all selected cells.

Denis
 

Forum statistics

Threads
1,081,691
Messages
5,360,644
Members
400,591
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top