Conditional Formatting Scuppers Macro

[rb3883]

I have the following Macro (below) copies red cells and pastes special however when the cells are red by conditional formatting it doesn't work, is there a way around this? Are all conditional formatting not picked up by the cell properties?

My Code is

Sub colourtest()
Dim Cell As Range
For Each Cell In Selection
If Cell.Interior.ColorIndex = 3 Then
Cell.Copy
Cell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End If
Next Cell

End Sub

 

[jim may]

Maybe...

Cells.FormatConditions(1).Interior.ColorIndex = 3

 

[rb3883]

Hi Jim,

Thanks very much for your quick response.

Code now altered as below however I got the error:

Run-time error '1004':
Unable to get the color index property of the interiror class?

Any ideas? It seems like you're pointing me in the right direction though!

Thanks for your time, Ryan


Sub colourtest()
Dim Cell As Range
For Each Cell In Selection
If Cells.FormatConditions(1).Interior.ColorIndex = 3 Then
Cell.Copy
Cell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End If
Next Cell

End Sub

 

[Andrew Poulsom]

Maybe this will help you:

http://www.mrexcel.com/forum/showthread.php?p=1628252

 

[Peter_SSs]

I have the following Macro (below) copies red cells and pastes special however when the cells are red by conditional formatting it doesn't work, is there a way around this? Are all conditional formatting not picked up by the cell properties?

My Code is

Sub colourtest()
Dim Cell As Range
For Each Cell In Selection
If Cell.Interior.ColorIndex = 3 Then
Cell.Copy
Cell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End If
Next Cell

End Sub

Could you just use the same condition(s) in your nacro, that you have in the Conditional Formatting?

 

[rb3883]

Thanks Andrew and Peter both good solutions i'm sure I can solve this now. Your time is much appreciated.

Have a nice weeekend, Ryan

 

[jim may]

Ryan,
Thought I'd share some further understanding I got from your issue. Maybe it can benefit you in solving your issue. First my original post was Cells.FormatConditions(1),...
when it should have been Cell.FormatConditions(1)... That however is a mute point because I discovered that in the course of Testing/looping through -- if there is No conditional formatting entered for a current cell under examination Excel raises a 1004 Error.

So I added the line On Error Resume Next
and the Line
If Err.Number > 0 Then
Else

thereby getting around it - So here's where I finished up...

Sub colourtest()
Dim Cell As Range
For Each Cell In Selection
On Error Resume Next
If Cell.FormatConditions(1).Interior.ColorIndex = 3 Then
If Err.Number > 0 Then
Else
MsgBox "Found C/F # 3 in Cell " & Cell.Address  'maybe replace this line with the 4 lines
                                                               'below that are currently commented out
End If
'Cell.Copy
'Cell.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
'xlNone, SkipBlanks:=False, Transpose:=False
'Application.CutCopyMode = False
End If
Next Cell
End Sub

Good Luck,

Jim

 

[rb3883]

Hi Jim,

That works great.

Many thanks