MS Excel - Counting on Cell Color

[tweaked2005]

I'm trying to create a column, that is populated with "Yes" if the cell background color for a different column is "Yellow". So in other words if:

If A1 Cell Color = Yellow, Then A2 = "Yes"

Is there any way at all I can do this in MS Excel?

Thanks in advance for any help.

 

[Bong]

clrval = Cells(1, 1).Interior.ColorIndex
if clrval=vbYellow then cells(2,1)="yes"

although, as you describe it, A1 and A2 are in the same column.

_________________
Bob Rashkin

 

[tweaked2005]

Forgive me for not knowing the answer to this. I've never coded VBA in Excel, only Access.

I now have a macro called colorCount and the code looks like this

Sub ColorCount()
  clrval=Cells(1,1).InteriorColorIndex
  If clrval=vbYellow Then Cells(2,2) = "yes"
End Sub

However, when I run the macro, it is not performing the task. Also, the next step will get it to look at the whole column (i.e. column A, and populate column B with "yes") so I'm guessing I will just have to revise to have it look at a range of cells.

Thanks again for your help.

 

[xlbo]

You need to loop through all the cells in the column:

Sub ColorCount()
dim lRow as long
lRow = cells(65536,1).end(xlup).row

for i = 2 to lRow
  if Cells(i,1).InteriorColorIndex = vbYellow then
      Cells(i,2).value = "yes"
  end if
next i
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tweaked2005]

I'm getting a Run-time error '438':

"Object doesn't support this property or method"

When I run the code you listed. I'll keep trying to fiddle with it.

Thanks again for all of your help with this.

 

[xlbo]

apologies:

if Cells(i,1).Interior.ColorIndex = vbYellow then


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question