vb for column backcolor

[MyD0j0]

TIA!

I have a simple schedule that I'm using an xltl for. How do I change
the backcolor of the column

I am calling the function from workbook_open()
Here is my function:

Private Function SetWeekends()

Dim wb As Workbook, sheet As Worksheet, list As Integer
Dim dt As String, yr As String, range As range, c
Dim i As Integer

Set wb = ThisWorkbook
yr = InputBox("Enter the 4 digit year this schedule " & vbCrLf & _
" is being created.", "Create Schedule")

If Len(yr) <> 0 Then 'in case we cancell the inputbox
With wb
For i = 1 To 12
Set range = .Worksheets(i).range("c2:ag2").Cells
For Each c In range
If IsNull(c.Value) = False Then 'for those months with
fewer than 31 days
dt = DateValue(.Worksheets(i).Name & " " & c.Value
& "," & yr)
If DatePart("w", dt) = 1 Or DatePart("w", dt) = 7
Then
.Column.BackColor = 12632256
End If
End If
Next c
Next i
End With
End If

Set wb = Nothing
End Function

 

[Nick Hodge]

There are a few things I would correct first

1) You should use a sub, not a function. In VBA a function cannot change or
manipulate objects, only return a value
2) You should explicitly define 'c' as a range object
3) I wouldn't use range as a variable, (not even sure if it will compile),
use something like rng.

Then not knowing what the backcolor you have quoted is (Excel VBA doesn't
have that property, this will set it to red

rng.EntireColumn.Interior.Color = RGB(255, 0, 0)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
web: www.excelusergroup.org

 

[MyD0j0]

Thanks for the assistance; you got me there!