CODEWITHSUNDEEP
excelvbacolors
Harriet Schofield
Harriet Schofield

Reputation: 1

IF/Then VBA Code based on cell colour. Not value

I'm sorry if this has been asked before. I have looked around and cannot seem to find the answer. I am new to VBA and would like some assistance.

Is it possible to to complete a IF/Then function on VBA based on colour, then replace the colour with a value.

Example:

If Cell.Interior.ColorIndex = 255 Then Cell.Value = 1

Some cells in the data table which are RED already have a value, others do not. I need to change them all to 1 so i can count them individually in a pivot table.

Thank you so much in advance

Upvotes: 0

Views: 5012

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Cell.Interior.ColorIndex is typically a value from 1 to 56 (the size of the current color pallette) and represents the position of a given color in the pallette.

The Cell.Interior.Color property is a Long value representing an RGB color value. Here 255 would be red - same as the value of vbRed

So

If Cell.Interior.ColorIndex = 255 Then Cell.Value = 1

should be

If Cell.Interior.Color = 255 Then Cell.Value = 1

Upvotes: 1

Related Questions