VBA Cell Highlighting -- Tricky

[Guest]

I am attempting to highlight four cells in a
particular row based upon which dealer is
chosen by a user. For the sake of simplicity
here is a sample table of similar data that
we are using:

Rank Dealer State % of Total
1 Acme CO 12.5
2 ABC KS 11.7
3 Wildcat Paint MO 9.4
4 Harris Co. FL 7.1

While I can highlight the dealer name fairly easy,
I'm having trouble having the rank highlight based
on if the dealer is highlighted.

For example, if the dealer name "Acme" was in cell E56
and was highlighted, then I'd like to also have the rank
"1" in cell D56 also highlight based on the fact that E56
was highlighted. If I can figure out how to highlight the
rank, then that will make doing the state and % of total
very easy to complete.

Here is the "rookie" code that I'm using to highlight the
dealer name however, I can't get the rank to highlight.

Thanks in advance for your help!

James

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

Private Sub Worksheet_Activate()
'Highlight active dealer in the ranked dealer list.

Dim cel1 As Range
Dim cel2 As Range
Dim wsSource As Worksheet
Dim strDealer As String

Set wsSource = Sheets("Dealer")
strDealer = wsSource.Range("C2")

'This block will highlight the dealer name
For Each cel1 In Range("E56:E70").Cells
If cel1.Value = strDealer Then
cel1.Interior.ColorIndex = 44
cel1.Font.FontStyle = "Bold"
Else
cel1.Interior.ColorIndex = 0
cel1.Font.FontStyle = "Regular"
End If
Next

'This block will hightlight the rank, one
'cell to the left of the dealer name, if
'the dealer name is highlighted.
For Each cel2 In Range("D5670").Cells
For Each cel1 In Range("E56:E70").Cells
If cel1.Value = strDealer Then
cel2.Interior.ColorIndex = 44
cel2.Font.FontStyle = "Bold"
Else
cel2.Interior.ColorIndex = 0
cel2.Font.FontStyle = "Regular"
End If
Next
Next

End Sub

 

[JE McGimpsey]

one could do this with code, but you could also use Conditional
Formatting:

Select D56:E70, with D56 active, then choose Format/Conditional
Formatting and enter:

CF1: =$E56=Dealer!$C$2
Format: <pattern>/color44, <font>/<bold>

 

[Guest]

Private Sub Worksheet_Activate()
'Highlight active dealer in the ranked dealer list.

Dim cel1 As Range
Dim cel2 As Range
Dim wsSource As Worksheet
Dim strDealer As String

Set wsSource = Sheets("Dealer")
strDealer = wsSource.Range("C2")

'This block will highlight the dealer name
For Each cel1 In Range("E56:E70").Cells
If cel1.Value = strDealer Then
With cel1.offset(0,-1).Resize(1,4)
.Interior.ColorIndex = 44
.Font.FontStyle = "Bold"
End With
Else
With cel1.offst(0,-1).Resize(1,4)
.Interior.ColorIndex = 0
.Font.FontStyle = "Regular"
End With
End If
Next


End Sub