Turning a coloured cell into a number

[laniw75]

Good afternoon, my learned friends.

I need some vba (or a conditional formatting formula) to add a 1 into all the cells that are red. Can anyone assist.

Thanks in advance

 

[Ruddles]

You can't change the contents of a cell using conditional formatting - that can only change its formatting.

The following code goes through every cell in the active sheet and if the background colour is red, it wil add one to its value:-

Option Explicit
 
Sub AddOneToRedCells()
 
  Dim oCell As Range
 
  For Each oCell In ActiveSheet.UsedRange
    If oCell.Interior.Color = vbRed Then oCell = oCell.Value + 1
  Next oCell
 
End Sub

When say "add a 1 into", do you mean "put 1 into" or "add 1 to". I've assumed the latter but I'm sure you can modify the line in question if you actually want the former.

 

[laniw75]

Hi R,

Thanks for you help. I am still having some issues. The background colour is actually a pink which I have adjusted in the code and although there are no errors when I run the macro, the pinks aren't entering a 1. Here is a copy of the exact code I am using, can you see where I am going wrong? The cells are blank apart from the formatting.

Option Explicit

Sub AddOneToRedCells()

Dim oCell As Range

For Each oCell In ActiveSheet.UsedRange
If oCell.Interior.Color = RGB(255, 153, 153) Then oCell = oCell.Value + 1
Next oCell

End Sub

 

[jeffreybrown]

It works fine for me!

Are you sure you have the color right?

Did you set the color yourself to the RGB? That's what I did and it works fine.

Sub AddOneToRedCells()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
        If oCell.Interior.Color = RGB(255, 153, 153) Then oCell = oCell.Value + 1
    Next oCell
End Sub

 

[laniw75]

Hi Jeff,

I just ran it again and it works beautifully, I still had the vba window open. Thank you so much for you help!

 

[jeffreybrown]

Glad you got it working

 

[laniw75]

Hi all excel genius's, I have an addendum to this

I need the red cell to reformat to general before it overrides with a numeral one.

Some cells have a date in them which just makes my macro add a day!

Option Explicit

Sub AddOneToRedCells()
Dim oCell As Range
For Each oCell In ActiveSheet.UsedRange
If oCell.Interior.Color = RGB(255, 153, 153) Then oCell = oCell.Value + 1
Next oCell
End Sub