shading rows with specific values???

[gr777]

I see plenty of articles on shading alternate rows and such. This is
what I'm trying to do. I have 8 account numbers that appear in
different rows daily. I would like to either use conditional formatting
or a macro that would look for the account number, and shade only that
row. Account numbers are like 80506148 as an example. Any suggestions??
Thanks

 

[Don Guillett]

For 8 different numbers/colors you could use a select case. Something like.
for each c in selection
Select Case c.value
Case > 3
'code here
Case 0,3
'code here
Case 2
'code here
Case Else
'code here
End Select
next c

--
Don Guillett
SalesAid Software
(e-mail address removed)

I see plenty of articles on shading alternate rows and such. This is
what I'm trying to do. I have 8 account numbers that appear in
different rows daily. I would like to either use conditional formatting
or a macro that would look for the account number, and shade only that
row. Account numbers are like 80506148 as an example. Any suggestions??
Thanks


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[Jim]

Here are a couple of solutions:
For text entries: (Change the "Text#" entries to meet your needs)

One way... copy the text below, press Alt+F11 to start the VBE Editor, then
double-click on the Sheet1 icon and paste this in:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim DropDownRange As Range
Dim ShadedRange As Range

If Target.Cells.Count > 1 Then Exit Sub

Set DropDownRange = Range("a1", Cells(Rows.Count, "a").End(xlUp))
If Intersect(Target, DropDownRange) Is Nothing Then Exit Sub

Set ShadedRange = Range(Cells(Target.Row, "a"), Cells(Target.Row, "N"))
With ShadedRange.Interior
Select Case (Target.Value)
Case "Text1": .ColorIndex = 36
Case "Text2": .ColorIndex = 37
Case "Text3": .ColorIndex = 38
Case "Text4": .ColorIndex = 39
Case "Text5": .ColorIndex = 40
Case "Text6": .ColorIndex = 41
Case Else: .ColorIndex = xlNone
End Select
End With

End Sub
..........................................
Or this for numerical entries.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim DropDownRange As Range
Dim ShadedRange As Range

If Target.Cells.Count > 1 Then Exit Sub

Set DropDownRange = Range("a1", Cells(Rows.Count, "a").End(xlUp))
If Intersect(Target, DropDownRange) Is Nothing Then Exit Sub

Set ShadedRange = Range(Cells(Target.Row, "a"), Cells(Target.Row, "N"))
With ShadedRange.Interior
Select Case (Target.Value)
Case 1: .ColorIndex = 36
Case 2 To 10: .ColorIndex = 37
Case 11 To 15: .ColorIndex = 38
Case 16 To 25: .ColorIndex = 39
Case 25 To 50: .ColorIndex = 40
Case Is > 50: .ColorIndex = 41
Case Else: .ColorIndex = xlNone
End Select
End With

End Sub