Question Locked

Formatting colour of row using cell in row.

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

3 replies

by purpleskys

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

by lukcasem

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
Wend

Range("A1").Select

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.

This discussion is closed to new replies.