Format row based on cell value

L

Linc

I would like to format a row based on the value of a cell in
that row. In some cases I would like to format only some
of the cells in that row and leave others alone.

Don't give me the complete answer, I am trying to work this
out, but I don't know what to use to select the range before
I change the format. And I don't know what to use to exclude
certain cells. If it helps, based on which row it is I will kno
which
cells are not to be formated.

Thanks

Lin
 
B

Bernie Deitrick

Linc,

There is no need to select cells to apply formatting.

You could do something like

Dim myRow As Long
myRow = ActiveCell.Row
If ActiveCell.Value = "Red" Then
Cells(myRow, 2).Interior.ColorIndex = 3
Cells(myRow, 5).Interior.ColorIndex = 3
End If
If ActiveCell.Value = "Blue" Then
Range(Cells(myRow, 1), Cells(myRow, 12)) _
.Interior.ColorIndex = 5
End If

HTH,
Bernie
MS Excel MVP
 
L

Linc

Thanks for the reply, I can try this but I need
a little bit of help. Can you tell me what this part
is doing

<SNIP>
Dim myRow As Long
myRow = ActiveCell.Row
<END SNIP>


Lin
 
L

Linc

Oh how cool ! I am very excited. With your help I figure out a fe
things and well, used part of your code to get to the following and
am very happy. Not done with this yet but this part is working great.

<SNIP CODE>
Private Sub Worksheet_Calculate()

Application.ScreenUpdating = False

For y = 4 To 38
Cells(y, 10).Select
A_Done = Cells(y, 10)
Dim ActiveRow As Long
ActiveRow = ActiveCell.Row
If ActiveCell.Value = "x" Then
Range(Cells(ActiveRow, 1), Cells(ActiveRow, 8)).Interior.ColorInde
= 4
End If
If ActiveCell.Value = "x1" Then
Range(Cells(ActiveRow, 1), Cells(ActiveRow, 8)).Interior.ColorInde
= 4
End If
If ActiveCell.Value = "x2" Then
Range(Cells(ActiveRow, 1), Cells(ActiveRow, 8)).Interior.ColorInde
= 4
End If
If ActiveCell.Value = "x3" Then
Cells(ActiveRow, 1).Interior.ColorIndex = 4
Cells(ActiveRow, 2).Interior.ColorIndex = 4
Range(Cells(ActiveRow, 5), Cells(ActiveRow, 8)).Interior.ColorInde
= 4
End If
If ActiveCell.Value = "x4" Then
Cells(ActiveRow, 1).Interior.ColorIndex = 4
Cells(ActiveRow, 3).Interior.ColorIndex = 4
Cells(ActiveRow, 8).Interior.ColorIndex = 4
End If
If ActiveCell.Value = "o" Then
Range(Cells(ActiveRow, 1), Cells(ActiveRow, 8)).Interior.ColorInde
= 0
End If
If ActiveCell.Value = "o1" Then
Range(Cells(ActiveRow, 1), Cells(ActiveRow, 8)).Interior.ColorInde
= 0
End If
If ActiveCell.Value = "o2" Then
Range(Cells(ActiveRow, 1), Cells(ActiveRow, 8)).Interior.ColorInde
= 0
End If
If ActiveCell.Value = "o3" Then
Cells(ActiveRow, 1).Interior.ColorIndex = 0
Cells(ActiveRow, 2).Interior.ColorIndex = 0
Range(Cells(ActiveRow, 5), Cells(ActiveRow, 8)).Interior.ColorInde
= 0
End If
If ActiveCell.Value = "o4" Then
Cells(ActiveRow, 1).Interior.ColorIndex = 0
Cells(ActiveRow, 3).Interior.ColorIndex = 0
Cells(ActiveRow, 8).Interior.ColorIndex = 0
End If
' If A_Done = "x" Then
' Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
' ActiveCell.Select
' With Selection
' .Interior.ColorIndex = 4
' .Interior.Pattern = xlSolid
' .Font.ColorIndex = 4
' End With
' ElseIf A_Done = "o" Then
' Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
' ActiveCell.Select
' With Selection
' .Interior.ColorIndex = 0
' .Interior.Pattern = xlSolid
' .Font.ColorIndex = 2
' End With
' End If
Next y

Application.ScreenUpdating = True

End Sub

<END CODE SNIP>

I left in the commented out section to show what I was working wit
before. The new code is working great. The varible in the begining o
'y' has to be manually edited and I would like to find a way for it t
be automatic or for it to pull it from a cell where maybe I can put
number. This way I wouldn't have to edit the Vba script everytime I ad
rows to the document.

Thanks million - I am really enjoying this stuff.

Linc

P.S. Question - I click the box for 'email notification' when I pos
but I never get an email to tell me there was a reply
 
B

Bernie Deitrick

Linc,
I left in the commented out section to show what I was working with
before. The new code is working great. The varible in the begining of
'y' has to be manually edited and I would like to find a way for it to
be automatic or for it to pull it from a cell where maybe I can put a
number. This way I wouldn't have to edit the Vba script everytime I add
rows to the document
Click to expand...

Typically, the way to do that is to find the bottom cell by using an End-Up
combination:

For y = 4 To Cells(65536, 10).End(xlUp).Row

HTH,
Bernie
MS Excel MVP
 
Top