Highlight Cell when value reaches every interval of 500

[Walt Matchim]

I have an accumulative value (miles from odometer) entered into a different cell of a column daily. I would like to have the cell background to be highlighted in Red when the miles reach intervals of 500 or the next entry over. This would indicate that the vehicle reauires service.
Example: 220, 356, 512, 678, 876, 1024, 1344 ...
The cell containing 512 would be highlighted because it reached another 500 miles. Same with 1024.

Hope you understand. Please help.

Thank you

Walt

[ilanr01]

I have an accumulative value (miles from odometer) entered into a different cell of a column daily. I would like to have the cell background to be highlighted in Red when the miles reach intervals of 500 or the next entry over. This would indicate that the vehicle reauires service.
Example: 220, 356, 512, 678, 876, 1024, 1344 ...
The cell containing 512 would be highlighted because it reached another 500 miles. Same with 1024.

Hope you understand. Please help.

Thank you

Walt

Use Format/Conditional formating "formula is" with =INT(A2/500)<>INT(A1/500), A1 being a Non-Number cell and A2 the first cell in your column. Set the color in the Conditional formating window as needed
Copy A2 format down the column
Ilan

[jackyg]

I have a similar question. In my spreadsheet I want it to highlight everything under 100 and the cells already contain formulas. How do you do that?

Thanks!

[Walt Matchim]

Use Format/Conditional formating "formula is" with =INT(A2/500)<>INT(A1/500), A1 being a Non-Number cell and A2 the first cell in your column. Set the color in the Conditional formating window as needed
Copy A2 format down the column
Ilan

Works great except that it turns 2 cells Red. (Active and the cell directly below)
Do I need additional Conditional Formating to turn off highlighted cell.

Thanks
Walt

[Dave Peterson]

Select your cells
format|conditional formatting
cell value is: less than: 100

Give it a nice format.

jackyg wrote:
>
> I have a similar question. In my spreadsheet I want it to highlight
> everything under 100 and the cells already contain formulas. How do you
> do that?
>
> Thanks!
>
> --
> jackyg
> ------------------------------------------------------------------------
> jackyg's Profile: http://www.excelforum.com/member.php...o&userid=26898
> View this thread: http://www.excelforum.com/showthread...hreadid=401167

--

Dave Peterson

[ilanr01]

Works great except that it turns 2 cells Red. (Active and the cell directly below)
Do I need additional Conditional Formating to turn off highlighted cell.

Thanks
Walt

Conditional Formating doe's not affect the active cell for being active, it only works when the condition is fulfilled whether the cell is active or not.
ilan

[Walt Matchim]

One Final Question:

Could you please change the formula to color 1 cell only. (not 2)
Maybe an IF Statement.

Walt

[ilanr01]

One Final Question:

Could you please change the formula to color 1 cell only. (not 2)
Maybe an IF Statement.

Walt

The best I came up with is this
Insert an additional column, say C and enter there =INT(A2/500), A being your data column
Then change the conditional format formula to:
=AND(INT(A2/500)<>INT(A1/500),INT(A2/500)=MAX($C$2:$C$8))
Lines 2 to 8 being your range
Hope this is what you meant
Ilan

[Ms. A**]

Is any one can help to do this? I want to highlight the cell every time it reaches to the interval, is it possible?

[AliGW]

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread