G
Guest
This is a database spreadsheet with the first row as the field headers.
I need to get the first Division number and then keep comparing it with the
other division numbers until I hit the next one. If it is the first mention
then I need to insert a gray header row. Can you help me with the loop?
Public Sub ColorRowHeaders()
'GREY ROW HEADER FOR FIRST ROW OF A DEPARTMENT IN A DIVISION
'Database is sorted on Division Number, Department Number and status
'Create row header in light gray above the first row
'of each consecutive Department in a Division
'cell 0 = division #
'cell P = division name
'cell Q = department #
'cell R = department name
' Create a medium grey row header above first row of every status type
within a department
'Cell Q1 status column ,Take Down, Continue, or Replace
'put a pagebreak at the end of every division
Dim lrow As Long
Dim rng As Range
Dim prevDivsion# As Integer
Dim Division# As Integer
lrow = 2
Set rng = Range("A&lrow")
With rng
Do While lrow <> .Cells(Rows.Count, "A").End(xlUp)
Division# = .Cells("0 &lrow")
If .Cells("0 &lrow ").Value <> prevDivision
Insert Row
Selection.Interior.ColorIndex = Gray
Header:= .cells(" Department name (cell,P &lrow)
lrow = lrow + 1
End while
End Sub
thanks,
I need to get the first Division number and then keep comparing it with the
other division numbers until I hit the next one. If it is the first mention
then I need to insert a gray header row. Can you help me with the loop?
Public Sub ColorRowHeaders()
'GREY ROW HEADER FOR FIRST ROW OF A DEPARTMENT IN A DIVISION
'Database is sorted on Division Number, Department Number and status
'Create row header in light gray above the first row
'of each consecutive Department in a Division
'cell 0 = division #
'cell P = division name
'cell Q = department #
'cell R = department name
' Create a medium grey row header above first row of every status type
within a department
'Cell Q1 status column ,Take Down, Continue, or Replace
'put a pagebreak at the end of every division
Dim lrow As Long
Dim rng As Range
Dim prevDivsion# As Integer
Dim Division# As Integer
lrow = 2
Set rng = Range("A&lrow")
With rng
Do While lrow <> .Cells(Rows.Count, "A").End(xlUp)
Division# = .Cells("0 &lrow")
If .Cells("0 &lrow ").Value <> prevDivision
Insert Row
Selection.Interior.ColorIndex = Gray
Header:= .cells(" Department name (cell,P &lrow)
lrow = lrow + 1
End while
End Sub
thanks,