Formatting colour of row using cell in row.

By lukcasem ·
I am trying to use conditional formatting to colour code my worksheet by status of my jobs.

I am not sure where to start at this stage as I am new to Excel 2007.

I wish to use five different colours which correspond to the status levels. Each job has it's own row.

Please help.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

try custom formatting

by PurpleSkys In reply to Formatting colour of row ...

maybe? I'm still learning Excel'07 as well...after you select custom formatting, a box will come up, choose the fill tab and choose a color. Hope that's what you were looking for

Collapse -

Conditional Formatting- Background Colours to rows.

by lukcasem In reply to Formatting colour of row ...

Thanks to some friendly help I was able to find what I needed.

Below, after ---Macro Start---, is a macro that will set the background color of a row based on the value in a given cell.

The macro is as follows and some comments in to make it a bit easier to read. (note comment lines starting with ' )

--- Macro Start ---

Sub Update_Row_Colors()

Dim LRow As Integer
Dim LCell As String
Dim LColorCells As String
' Start at row 1
LRow = 1

' This will look at the first 2000 rows, if you have more than 2000, change this value
While LRow < 2000

' This sets the column for which you are testing, in this example, column C
LCell = "C" & LRow

' Row background color will be changed in columns A to K, if your columns extend past K, change the below "K" to the appropriate column

LColorCells = "A" & LRow & ":" & "K" & LRow

' Indicates how many characters in the column you want to evaluate
Select Case Left(Range(LCell).Value, 6)

' Set row color to light blue if column value = 007007
Case "007007"
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

' Set row color to light blue if column value = 030087
Case "030087"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

' Set row color to light blue if column value = 063599
Case "063599"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 19
Range(LColorCells).Interior.Pattern = xlSolid

' Default all other rows to no color
Case Else
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = xlNone

End Select

LRow = LRow + 1


End Sub

-- Macro End --

All you should need to do is add the code (blue text) to the spreadsheet, by going to Tools -> Macro -> Visual basic Editor, paste the above code, change the values to suit your requirements and then click the run button. That should do the trick.

Related Discussions

Related Forums