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 ravi.m@in.yokogawa.com

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.

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

Hi, 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: =IF(MONTH($B3)=MONTH(NOW()),TRUE,FALSE) 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 =IF(MONTH($B3)=MONTH(NOW())+1,TRUE,FALSE 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. Regards

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 Next 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!