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!

 

[Dave Peterson]

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

Give it a nice format.

 

[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

 

[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),
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
Ila