how to define color of the cell by coding

[maryam]

I want to change the color of my cells based on the value. how can I write it in VBA?
If worksheets("sheet1").cells(1,i).value=1 then
worksheets("sheet1").cells(1,i).color=blue


how to write the bold line?

[JimmyTheHand]

Hi Mary

Three options for you:

1.)
worksheets("sheet1").cells(1,i).interior.colorindex = 5

When you record a macro that changes cell color to something, you get a code that changes colorindex. Each color has a colorindex, that you can determine by recording a macro that changes a cell's color as desired. You can then use this colorindex with method #1 above.

2.)
worksheets("sheet1").cells(1,i).interior.color=vbBlue

With method #2 you use built-in constants, which are not too many, a dozen or so, AFAIK, but they are easily recognizable (vbBlue, vbBlack, vbRed, etc).

3.)
worksheets("sheet1").cells(1,i).interior.color = RGB(0, 0, 255)

With RGB function, you can blend any color you want by varying the amount of Red, Green and Blue components in the blend.

HTH

Jimmy

[Bob Phillips]

3.)
worksheets("sheet1").cells(1,i).interior.color = RGB(0, 0, 255)

With RGB function, you can blend any color you want by varying the amount of Red, Green and Blue components in the blend.

Not correct. Excel will map it to the closest match of colours on the colour palette. You have 56 in pre-2007 Excel, that's it.