VBA If Range ...

[Pinaceous]

Hi All,

I'm looking for a vba code upon to run upon Range A1:D5 where if there is an interior color red cell and it contains a value that the sub will remove its interior color for that range.

For example, if I have the following situation and then I run the sub,

It will return:

Can someone please help me?

Thank you!
pinaceous

 

[Michael M]

Hi Pin
How is the Red color placed there in the first place ?
Is it CF or manually input ?

 

[Rick Rothstein]

Give this macro a try...

Sub IfRedAndValueRemoveRed()
  Dim Cell As Range
  For Each Cell In Cells.SpecialCells(xlConstants)
    If Cell.Interior.Color = vbRed And Cell.Value <> "" Then
      Cell.Interior.Color = xlNone
    End If
  Next
End Sub

 

[Pinaceous]

Give this macro a try...

Sub IfRedAndValueRemoveRed()
  Dim Cell As Range
  For Each Cell In Cells.SpecialCells(xlConstants)
    If Cell.Interior.Color = vbRed And Cell.Value <> "" Then
      Cell.Interior.Color = xlNone
    End If
  Next
End Sub

Rick Rothstein!

Thank you very much for responding to my post!

Curious, do you know how I can specify a specific range?

For example, Range A1:D5.

Please let know,

Thank you,
pinaceous

 

[Rick Rothstein]

Curious, do you know how I can specify a specific range?

For example, Range A1:D5.

Try this way...

Sub IfRedAndValueRemoveRed()
  Dim Cell As Range
  For Each Cell In Range("A1:D5")
    If Cell.Interior.Color = vbRed And Cell.Value <> "" Then
      Cell.Interior.Color = xlNone
    End If
  Next
End Sub

 

[Pinaceous]

Thank you so much Rick!!!