Simple vba for tagging colored cells

[joelle]

Hello Experts,

Could you please help me with some simple VBA code for the following:
In col A, range A1:A20, I have random cells that are green (color index =4).
Then:
- If A9 is green, I would like B9 to show the letter "a"
- If A15 is green, I would like B15 to show the letter "a"
and so on so forth -- I only use letter "a" to tag the green cells in col A.

Many thanks in advance.

joelle

[Bob Phillips]

Use conditional formatting, no need for code. Look it up in help.

[mikerickson]

XLD, what condition would you test for. I thought that there is no spreadsheet way to test for color.

[joelle]

Xld,

The random gree cells in col A is the result of conditional formatting.
Then what I tried to do is if a cell in col A is green, the same cell in col B is automatically tag with a letter "a"

I'm using Excel 2000, or else I know Excel 2007 can filter by cell color.

Additional help from you or others is very much appreciated.

joelle

[mikerickson]

Testing for color that is the result of conditional formatting is difficult. It would be easiest for colB to test the same conditions as col A does rather than testing if col. A's conditional formatting has tripped.

[joelle]

wow, wow
I thought this was simple - I still think it is for VBA experts.

The code will chech for range A1:A20 to see:
* if any cell has color index = 4,
* if yes, the cell across in colB will have the value "a"
* Else, do nothing

but I dont know how to translate English into vba.

Help!

joelle

[Bob Phillips]

This is not going to be easy, but it can be done.

Take a look at http://www.xldynamic.com/source/xld.CFConditions.html.

mikerickson's suggestion is simpler though.

[lucas]

What Mike is asking is....what is turning the cells in column A green and use that same condition to put an a in column b instead of using the color index....

I'm thinking you're using Marlett font formatting for column B so it shows as a checkmark?

[Robert]

I wrote something like this before, I use it to check my data update or not, maybe you can try on this.


Sub checkColor1()
Dim a, b
For a = 1 To 20
Cells(a, 1).Select
b = Selection.Interior.ColorIndex
If b = 4 Then
Cells(a, 2)= "OK"
End If
Next
End Sub

If you want to change the color, you can also write on that:


If b = 4 Then
Selection.Interior.ColorIndex = 7
End If

[Bob Phillips]

I think the OP is looking to check conditional formatting colour, not manual colouring.