Formatting cells with VB

[Carrie_Loos via OfficeKB.com]

Hi -

I have a long string of code that basically goes through each row and
determines a color for it based on a few variables. [Training Classes;
Completed, Cancelled and Segregate by Class Date] There are several tabs for
different customers and the third pass for color changes based customer tab.
The best way I could think of doing this was Run through the worksheet three
times:

First Pass: If some text is Like "*Complete*" Set the row color to gray
Second Pass: If class end date and customer name change then Set row color =
#
Third Pass: If some text is Like "*CANCELLED*" Set the row color to red

What this does is create blocks of colors for each class which may have
grayed lines in them or not. The top row a new class with the second pass. I
need to bold the second pass row which I can do simply enough but how do I
also set a set a border around each instance [or training class] of the color
with the second pass?

Sheets("Customer1").Select
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=">=1/1/2008", Operator:=xlAnd
Range("L2").Select
Selection.End(xlDown).Select
ActiveCell.Select
ActiveCell.Offset(1, -1).Activate
ActiveCell.FormulaR1C1 = "999"
ActiveCell.Offset(0, -5).Activate
ActiveCell.FormulaR1C1 = "stop"
Range("K2").Select

Do Until ActiveCell = "999"

If ActiveCell.Value Like "*complete*" Then
Selection.EntireRow.Interior.ColorIndex = 15
ActiveCell.Offset(1, 0).Activate

Else
If ActiveCell.Offset(1, 0) = Null Then
ActiveCell.Offset(1, 0).Activate

Else: ActiveCell.Offset(1, 0).Activate

End If
End If
Loop

Range("F2").Select

Do Until ActiveCell = "stop"

If ActiveCell = ActiveCell.Offset(1, 0) And ActiveCell.Offset(0, -2) =
ActiveCell.Offset(1, -2) Then
ActiveCell.Offset(1, 0).Activate

Else
If ActiveCell.Offset(1, 0) = Null Then
ActiveCell.Offset(1, 0).Activate

Else: ActiveCell.Offset(1, 0).EntireRow.Interior.ColorIndex = 37
ActiveCell.Offset(1, 0).Activate

End If
End If
Loop

Range("K2").Select

Do Until ActiveCell = "999"

If ActiveCell.Value Like "*CANCELLED*" Or ActiveCell.Value Like "*NOT
FUNDED" Then
Selection.EntireRow.Interior.ColorIndex = 3
ActiveCell.Offset(1, 0).Activate

Else
If ActiveCell.Offset(1, 0) = Null Then
ActiveCell.Offset(1, 0).Activate

Else: ActiveCell.Offset(1, 0).Activate

End If
End If
Loop

Columns("M:M").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Interior.ColorIndex = xlNone
Range("a1").Select


Any Ideas would be greatly appreciated.
Carrie

 

[Don Guillett]

Something like this should be a bit easier. change mc="a" to your search
column & colors to suit.
Sub selectcolor()
mc = "a"
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row
Select Case UCase(Cells(i, mc))
Case "CANCELLED": s = 35
Case "COMPLETED": s = 6
Case "C": s = 12
Case Else
s = 0
End Select
Rows(i).Interior.ColorIndex = s
Next i
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Hi -

I have a long string of code that basically goes through each row and
determines a color for it based on a few variables. [Training Classes;
Completed, Cancelled and Segregate by Class Date] There are several tabs
for
different customers and the third pass for color changes based customer
tab.
The best way I could think of doing this was Run through the worksheet
three
times:

First Pass: If some text is Like "*Complete*" Set the row color to gray
Second Pass: If class end date and customer name change then Set row color
=
#
Third Pass: If some text is Like "*CANCELLED*" Set the row color to red

What this does is create blocks of colors for each class which may have
grayed lines in them or not. The top row a new class with the second pass.
I
need to bold the second pass row which I can do simply enough but how do I
also set a set a border around each instance [or training class] of the
color
with the second pass?

Sheets("Customer1").Select
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=6, Criteria1:=">=1/1/2008", Operator:=xlAnd
Range("L2").Select
Selection.End(xlDown).Select
ActiveCell.Select
ActiveCell.Offset(1, -1).Activate
ActiveCell.FormulaR1C1 = "999"
ActiveCell.Offset(0, -5).Activate
ActiveCell.FormulaR1C1 = "stop"
Range("K2").Select

Do Until ActiveCell = "999"

If ActiveCell.Value Like "*complete*" Then
Selection.EntireRow.Interior.ColorIndex = 15
ActiveCell.Offset(1, 0).Activate

Else
If ActiveCell.Offset(1, 0) = Null Then
ActiveCell.Offset(1, 0).Activate

Else: ActiveCell.Offset(1, 0).Activate

End If
End If
Loop

Range("F2").Select

Do Until ActiveCell = "stop"

If ActiveCell = ActiveCell.Offset(1, 0) And ActiveCell.Offset(0, -2) =
ActiveCell.Offset(1, -2) Then
ActiveCell.Offset(1, 0).Activate

Else
If ActiveCell.Offset(1, 0) = Null Then
ActiveCell.Offset(1, 0).Activate

Else: ActiveCell.Offset(1, 0).EntireRow.Interior.ColorIndex = 37
ActiveCell.Offset(1, 0).Activate

End If
End If
Loop

Range("K2").Select

Do Until ActiveCell = "999"

If ActiveCell.Value Like "*CANCELLED*" Or ActiveCell.Value Like "*NOT
FUNDED" Then
Selection.EntireRow.Interior.ColorIndex = 3
ActiveCell.Offset(1, 0).Activate

Else
If ActiveCell.Offset(1, 0) = Null Then
ActiveCell.Offset(1, 0).Activate

Else: ActiveCell.Offset(1, 0).Activate

End If
End If
Loop

Columns("M:M").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Interior.ColorIndex = xlNone
Range("a1").Select


Any Ideas would be greatly appreciated.
Carrie