VBA code to hightlight range of cells based on the value of anotheradjacent cell

[aldo jr]

All,

I'd like to know if it is possible to make a VBA code for exel that
highlights a range of cells based on the value of a particular cell.

Sample data:


ALPHA Street Low High City Zip SEQ

A Ahasta 1 98 SOUTH SAN FRANCISCO 94080 200
A Aherwood 2401 2499 SOUTH SAN FRANCISCO 94080 190
B Blyline Dr 2601 2899 SOUTH SAN FRANCISCO 94080 475
B Bouthcliff 100 215 SOUTH SAN FRANCISCO 94080 140
C Couthcliff 217 398 SOUTH SAN FRANCISCO 94080 150
C Ctamford 2600 2698 SOUTH SAN FRANCISCO 94080 170
C Cetein 3900 3998 SOUTH SAN FRANCISCO 94080 160
D Dummit 1 98 SOUTH SAN FRANCISCO 94080 190
D Dunrise 1 98 SOUTH SAN FRANCISCO 94080 190
D Dutton 100 198 SOUTH SAN FRANCISCO 94080 120
D Dahoe 1 98 SOUTH SAN FRANCISCO 94080 200
W Wara 2400 2598 SOUTH SAN FRANCISCO 94080 190
W Wheresa 500 698 SOUTH SAN FRANCISCO 94080 130
..
..
..
n

From the sample data above, I'd like to highlight in yellow each cell
range letter break. For instance, i'd like for the streets that start
with the letter B to be highlighted along with it's corresponding
fields of Low, High, City, Zip, and Sequence number. I would not like
for the next set of streets; the C letter starting streets to be
highlighted since B and C are next to each other. But I would like
the same pattern to repeat for the streets that begin with the letter
D; to be highlighted along with their corresponding fields of Low,
High, City, Zip and Sequence.

Make sence?

 

[Bob Phillips]

Select all the cells involved, assuming starting at A1, and use conditional
formatting with a formula of

=$A2<>$A1

 

[Don Guillett]

One way of many.

Sub higlightbychar()
Dim c As Range
Dim ch, lr As Long
lr = Cells(rows.Count, "c").End(xlUp).row
For Each c In Range("c1:c" & lr)
For ch = 65 To 90 Step 2
If UCase(Left(c, 1)) = Chr(ch) Then
c.Resize(, 5).Interior.ColorIndex = 6
End If
Next ch
Next c
End Sub

 

[aldo jr]

Don the code worked great. I have two questions. 1) What extra code
do i need to insert if I also have numerical values in the C:C range?
right now it's identifying character values or letters. 2) What does
the code "For ch = 65 To 90 Step 2" mean? what is it doing?

Thank you!

 

[Don Guillett]

The code should be looking in the column of your street name. In this case,
probably col B.
It is looking for char 65 which is "A" to char 90 which is "Z" skipping
every other one. So street names starting with A will highlight, C, E, etc.