Changing all cells in one colour to a different colour

[Bob]

I have been provided with a workbook where alternate rows are highlighted in
a colour that makes the text difficult to read. I would like to change all
the cells with this colour to a lighter colour. Is this possible in an easy
manner?

 

[Ken Johnson]

I have been provided with a workbook where alternate rows are highlighted in
a colour that makes the text difficult to read. I would like to change all
the cells with this colour to a lighter colour. Is this possible in an easy
manner?

Conditional Formatting will override the applied fill.
If the dark rows are the odd numbered rows then use Formula Is...

=MOD(ROW(1:1),2)<>0

if it's the even numbered rows then use Formula Is...

=MOD(ROW(1:1),2)=0

and in either case apply a lighter coloured fill with the conditional
formatting.

Ken Johnson

 

[Bob]

Ken

Thanks for this. I should have looked more closely. It is not just
alternate rows. It is a number of rows with different gaps between, ie
3,5,7,12,14,etc. Your suggestion works but not for what I actually have. I
would like to identify all the cells of a particular colour wherever they
occur and then change them all. Is this possible?

 

[Ken Johnson]

Ken

Thanks for this. I should have looked more closely. It is not just
alternate rows. It is a number of rows with different gaps between, ie
3,5,7,12,14,etc. Your suggestion works but not for what I actually have. I
would like to identify all the cells of a particular colour wherever they
occur and then change them all. Is this possible?

If there are so many rows that it would take too long to Ctrl-Click
them then change there fill color then maybe run this macro to do
it...

Public Sub ChangeFill()
Dim rgDark As Range, Index As Integer, rgCell As Range
'Deal with press of Cancel Button
On Error GoTo CANCELLED
'Get user to select a cell with the dark fill
Set rgDark = Application.InputBox( _
Prompt:="Select a cell with the dark fill.", _
Title:="Lighten all rows with selected fill.", _
Default:=Selection.Cells(1).Address, _
Type:=8)
'Get the dark fill's ColorIndex
Index = rgDark.Interior.ColorIndex
'Reset for errors
On Error GoTo 0
'Find bottommost row in Column A with data
'Change the A's if column A is
'not part of the data
Dim lnLastrow As Long
lnLastrow = Range("A" & _
Range("A:A").Rows.Count).End(xlUp).Row
'Loop through column A cells and change
'dark rows to Tan (ColorIndex = 40)
Application.ScreenUpdating = False
For Each rgCell In Range("A1:A" & lnLastrow)
If rgCell.Interior.ColorIndex = Index Then
'Edit the ColorIndex value in next line
'to suit your needs.
'Run Public Sub FillColors() on a spare sheet
'to see the colors and corresponding ColorIndices
'in column A of the spare sheet
Let rgCell.EntireRow.Interior.ColorIndex = 40 'Tan
End If
Next rgCell
CANCELLED: 'User clicked Cancel button
End Sub

Following macro only need be used if Tan is not a suitable fill color
to change to.
If you run it on a spare empty sheet you will be able to see
ColorIndex value in column A and resulting color in Column B.

Public Sub FillColors()
For i = 0 To 56
With Range("A" & i + 1)
.Value = i
.Offset(0, 1).Interior.ColorIndex = i
End With
Next
End Sub


Ken Johnson