Format row based on cell value

  • Thread starter Thread starter Linc
  • Start date Start date
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
 
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
 
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
 
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
 
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
 
Back
Top