Re: Change based on date

Okay, so it should just be

Sub Macro2()
Dim i As Long
Dim rng As Range
Dim sh As Worksheet

For i = 1 To Sheets.Count

Set sh = Sheets(i)

Select Case sh.Range("B1").Value

Case #7/31/2008#: Set rng = sh.Range("C16:AB16")
Case #8/31/2008#: Set rng = sh.Range("C17:AB17")
Case Else: Set rng = Nothing

End Select

If Not rng Is Nothing Then

With rng

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With
End If
Next i
'
End Sub

Just add more Case statements for more dates/rows.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"lonnierudd via OfficeKB.com" <u11209@uwe> wrote in message
news:87d29e3ce5ec2@xxxxxx
It didn't work, probably because it isn't referring to where the date is?
Just guessing. The end of month date that is entered will be in cell B1
(sorry I didn't say that before). I can't have it based on NOW() since it
might be run at any time the following month. I haven't used the Case
function before, but it would certainly open up loads of learning for me.
I
appreciate your help and time looking at this.

Bob Phillips wrote:
Sub Macro2()
Dim i As Long
Dim rng As Range
Dim sh As Worksheet

For i = 1 To Sheets.Count

Set sh = Sheets(i)

Select Case Date

Case #7/27/2008#: Set rng = sh.Range("C16:AB16")
Case #8/31/2008#: Set rng = sh.Range("C17:AB17")
Case Else: Set rng = Nothing

End Select

If Not rng Is Nothing Then

With rng

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With
End If
Next i
'
End Sub

Hello,
I would like to be able to change the formatting of a limited row of
cells
[quoted text clipped - 53 lines]
'
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200807/1



.

Relevant Pages