Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Walt Matchim
 
Posts: n/a
Default Highlight Cell when value reaches every interval of 500


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 :)


--
Walt Matchim
------------------------------------------------------------------------
Walt Matchim's Profile: http://www.excelforum.com/member.php...o&userid=26885
View this thread: http://www.excelforum.com/showthread...hreadid=401167

  #2   Report Post  
ilanr01
 
Posts: n/a
Default


Walt Matchim Wrote:
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


--
ilanr01
------------------------------------------------------------------------
ilanr01's Profile: http://www.excelforum.com/member.php...o&userid=26797
View this thread: http://www.excelforum.com/showthread...hreadid=401167

  #3   Report Post  
jackyg
 
Posts: n/a
Default


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

  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #5   Report Post  
Walt Matchim
 
Posts: n/a
Default


ilanr01 Wrote:
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


--
Walt Matchim
------------------------------------------------------------------------
Walt Matchim's Profile: http://www.excelforum.com/member.php...o&userid=26885
View this thread: http://www.excelforum.com/showthread...hreadid=401167



  #6   Report Post  
ilanr01
 
Posts: n/a
Default


Walt Matchim Wrote:
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


--
ilanr01
------------------------------------------------------------------------
ilanr01's Profile: http://www.excelforum.com/member.php...o&userid=26797
View this thread: http://www.excelforum.com/showthread...hreadid=401167

  #7   Report Post  
Walt Matchim
 
Posts: n/a
Default


One Final Question:

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

Walt


--
Walt Matchim
------------------------------------------------------------------------
Walt Matchim's Profile: http://www.excelforum.com/member.php...o&userid=26885
View this thread: http://www.excelforum.com/showthread...hreadid=401167

  #8   Report Post  
ilanr01
 
Posts: n/a
Default


Walt Matchim Wrote:
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


--
ilanr01
------------------------------------------------------------------------
ilanr01's Profile: http://www.excelforum.com/member.php...o&userid=26797
View this thread: http://www.excelforum.com/showthread...hreadid=401167

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
copying cell names Al Excel Discussion (Misc queries) 12 August 11th 05 03:01 PM
When I am in one cell highlight another Jose Juan Excel Discussion (Misc queries) 2 August 3rd 05 09:04 AM
Cell color based upon cell value My View Excel Discussion (Misc queries) 11 July 6th 05 03:59 AM
Conditional Format to highlight blank cell DaveMoore Excel Worksheet Functions 4 June 23rd 05 10:10 AM
highlight range if cell contains desired data The Other Bollinger New Users to Excel 7 March 24th 05 02:30 AM


All times are GMT +1. The time now is 12:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"