changing excel cell color

Microsoft Excel 2003 (full product)
October 20, 2009 at 03:32:21
Specs: Windows XP
Dear sir,

This is Ravi .M
We have a list of calibrated test & measurement equipments for our testing activity, Presently we are maintaining calibration detail in one excel page( eg. Equepment name , Sl no , Calibration date , next calibration due date etc ) I need to change this excel page with below features.

1. Instrument within calibration due date it should be Green( or that Cell should be Green).
2. Cal due will be on next month it should Yellow ( or that Cell should be Green).
3. Cal due in this month it should be Red ( or that Cell should be Green).

With Regards
Ravi .M

See More: changing excel cell color

Report •

October 20, 2009 at 04:28:03
Hello, Ravi M. I think you are searching short method for your mentioned task. However its alternative is that you should use EXCEL-SHEET features to color a particular cell by using color option.

Report •

October 20, 2009 at 04:40:44
with worksheets(1)
if .range("A1").value = .range("B1") then .range("B1").interior.color = RGB(204,255,255)

end with

204, 255,255 -- light blue
153, 204, 255 -- blue
255, 255, 255 -- white
255,255, 204 -- yellow
153,204,255 -- green

hope this help

Report •

October 20, 2009 at 06:33:33

As you want cells to change based on whether the item is due for calibration this month, next month or more than a month away you need to use conditional formatting.

Start with formatting the cells Green.

Then use Conditional formatting to change the cell colours as the difference between due date (the value in the cell) and the date today changes.

Click on one of the cells with a calibration date
Click Format - ConditionalFormatting...
Under Condition 1 Select Formula Is
In the box enter the following formula:


Select formatting and change the pattern to Red (you may also want to change the font to white)

Now select Add and under Condition 2 select Formula Is and enter the following Formula


Select Format and a yellow pattern with black text
Click OK

If the date in cell B3 (in this example) is in the same month as today's date, the first formula will evaluate to TRUE (note true is not in double quotes), and will format the cell Red. If false, i.e., date is not in current month, the second formula is evaluated. The second formula tests if the date in B3 has the same Month as today + 1 month, i.e., next month. If the formula evaluates to TRUE the cell is formatted Yellow.

If both formulas evaluate to FALSE, the original cell formatting is unchanged, leaving the cell green.

Once one cell is setup, copy the cell, then Paste special - formats to all the other date cells.

If you Paste Special - Formats to other cells with equipment descriptions etc. they should also change to the same colours.

You could add a third condition for equipment that has passed its due date.


Report •

Related Solutions

October 20, 2009 at 07:08:23
Another way to color cells via VBA is to use the Interior.ColorIndex property instead of the RGB values.

Run this code to see the 56 colors available, along with the ColorIndex value for each color:

Sub WhatColor()
 For clrIndex = 1 To 56
  Range("A" & clrIndex).Interior.ColorIndex = clrIndex
 MsgBox "The Row Number Next To Each Color" & vbCrLf _
      & "Is The ColorIndex Value For That Color"
End Sub

Once you find a color you like, e.g. Dark Green, you can use something like:

Range("A1").Interior.ColorIndex = 10

That's 9 less characters to type!

Report •

Ask Question