Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing all cells in one colour to a different colour
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing all cells in one colour to a different colour
On Jun 25, 7:04 pm, Bob wrote:
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing all cells in one colour to a different colour
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? "Bob" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing all cells in one colour to a different colour
On Jun 25, 8:35 pm, Bob wrote:
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? "Bob" wrote: 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? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help Please.....numbers reaching limits and cells changing colour | Excel Worksheet Functions | |||
Changing fill colour of cells in a range | Excel Discussion (Misc queries) | |||
Changing font colour in some cells | New Users to Excel | |||
changing the colour of cells depending on the content. | Excel Discussion (Misc queries) | |||
Changing cell colour depending on another cells value... | Excel Discussion (Misc queries) |