I wrote some code that loops through worksheets looking for orange colored cells in range B5:B100. The reason for this is a long winded explaination but the basic reason is that these cells are used to flag over staffed positions. The cells are set to a height of 1.5 (almost hidden but still there). When data is thrown into them the row expands to 25. When finished fixing the staffing issue the sheets are cleared in preparation to receive new data. So far, all the code for doing the other tasks works. To summarize - loop through each sheet, look for orange cells in range B5:B100, change orange cells row height to 1.5.
Here is the code so far:
CODE
Sub OrangeRowsHt()
'sets rows that are orange to a height of 1.5
Dim WS_Count As Integer
Dim I As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
Dim TR As Range
Dim TC As Range
For I = 1 To WS_Count 'loop through worksheets
Worksheets(I).Select
Set TR = Range("B5:B100")
For Each TC In TR
If TC.Interior.Color = "Orange" Then
TC.RowHeight = 1.5
Else
'TC.RowHeight = 25
End If
Next TC
Next I
Sheets("Sheet1").Select
MsgBox "Finished fixing row heights!", vbOKOnly
End Sub
'sets rows that are orange to a height of 1.5
Dim WS_Count As Integer
Dim I As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
Dim TR As Range
Dim TC As Range
For I = 1 To WS_Count 'loop through worksheets
Worksheets(I).Select
Set TR = Range("B5:B100")
For Each TC In TR
If TC.Interior.Color = "Orange" Then
TC.RowHeight = 1.5
Else
'TC.RowHeight = 25
End If
Next TC
Next I
Sheets("Sheet1").Select
MsgBox "Finished fixing row heights!", vbOKOnly
End Sub
Thank you in advance for any assistance.
rpes