need help

  • Thread starter Thread starter bablu
  • Start date Start date
B

bablu

there are 3 columns in my excel worksheet. some cells in the first two
columns are red in color. these cells match. i want to select the cells
in the third column corresponding to these red cells in the first two
columns and make them red too. after this i want to select all the
cells which are not red in color in these three columns and make a new
worksheet out of them.
Can someone help? :confused:
 
Is this along the lines of what you're looking for? I'm assuming your first
two columns are A and B. If both of those cells are red (interior color, not
font color) then it will change the cell in the third column red also. The
second macro will copy anything that does not have a red interior to a new
worksheet. Both macros require you to select the third column before running.


Sub test1()
Dim x As Range

For Each x In Intersect(Selection, _
Selection.Parent.UsedRange)
If Cells(x.Row, "A").Interior.ColorIndex = 3 And _
Cells(x.Row, "B").Interior.ColorIndex = 3 Then _
x.Interior.ColorIndex = 3
Next x

End Sub


Sub test2()
Dim CopyRange As Range
Dim x As Range

For Each x In Intersect(Selection, _
Selection.Parent.UsedRange)
If x.Interior.ColorIndex <> 3 Then
If CopyRange Is Nothing Then
Set CopyRange = x
Else: Set CopyRange = Union(CopyRange, x)
End If
End If
Next x

If Not CopyRange Is Nothing Then _
CopyRange.Copy Worksheets.Add.Cells(1, 1)

End Sub
 
Back
Top