Need help altering VBA

[Toonies]

Hi I am trying to alter the following VBA code that I found in this forum
http://www.mrexcel.com/forum/showthread.php?t=539877&highlight=holiday+planner

What I am trying to do is the following

I input the employees contracted daily hours in worksheet("Hours") by day, Month for the full year, I then this populate to each monthly planner using the following formula C6 =IF(ISBLANK(Hours!C6),"",Hours!C6) etc.

the full range is C6:AG31

Excel Workbook
	A	B	C	D	E	F	G	H	I
1			2011
2			1	2	3	4	5	6	7
3			January 2011
4			Sat 01 Jan	Sun 02 Jan	Mon 03 Jan	Tue 04 Jan	Wed 05 Jan	Thu 06 Jan	Fri 07 Jan
6		employee01	8	3		2	3	4	5
Sheet

I am trying to amend the following VBA to highlight only ( insert interior.color) the cells that have been populated with numbers from the "Hours" worksheet and ignore the Blank cells.

Private Sub CommandButton1_Click()
Dim Rng As Range, Dn As Range
Dim sDt As Date
Dim eDt As Date
Dim Ac As Integer
Dim col As Integer
Set Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
sDt = ComboBox1
eDt = ComboBox2
 Select Case True
    Case Is = holidayButton1: col = 43
    Case Is = sickLeaveButton3: col = 53
    Case Is = otherOptionButton4: col = 37
 End Select
 For Each Dn In Rng
        If Dn = nameBox1 Then
            For Ac = 1 To 31 ' Change to 31
           If Weekday(Cells(4, Ac + 2), vbMonday) < 6 Then
                If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then
                   Dn.Offset(, Ac).Interior.ColorIndex = col
                End If
                End If
            Next Ac
        End If
    Next Dn
  Unload Me
End Sub

Is this Possible

I look forward to any replies

 

[wigi]

Hello there

Why don't you use conditional formatting in the Excel sheet rather than in VBA?

 

[Toonies]

The VBA would give me more options, I'm getting to understand VBA but still a novice

 

[Toonies]

Hi I am asking if the above VBA can ignore Blank "" Cells is it Possible

 

[wigi]

Perhaps (I'm far from sure since there are already other IF's in the code, which could have a similar effect):

Private Sub CommandButton1_Click()
Dim Rng As Range, Dn As Range
Dim sDt As Date
Dim eDt As Date
Dim Ac As Integer
Dim col As Integer
Set Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
sDt = ComboBox1
eDt = ComboBox2
 Select Case True
    Case Is = holidayButton1: col = 43
    Case Is = sickLeaveButton3: col = 53
    Case Is = otherOptionButton4: col = 37
 End Select
 For Each Dn In Rng
        If Dn = nameBox1 Then
            For Ac = 1 To 31 ' Change to 31
           If Cells(4, Ac + 2).Value <> "" Then
           If Weekday(Cells(4, Ac + 2), vbMonday) < 6 Then
                If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then
                   Dn.Offset(, Ac).Interior.ColorIndex = col
                End If
                End If
                End If
            Next Ac
        End If
    Next Dn
  Unload Me
End Sub

 

[Toonies]

Hi I have tried your suggestion it blanks the weekend cells even when they have numbers in but not the other blank cells

 

[wigi]

Try:

Private Sub CommandButton1_Click()
Dim Rng As Range, Dn As Range
Dim sDt As Date
Dim eDt As Date
Dim Ac As Integer
Dim col As Integer
Set Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
sDt = ComboBox1
eDt = ComboBox2
 Select Case True
    Case Is = holidayButton1: col = 43
    Case Is = sickLeaveButton3: col = 53
    Case Is = otherOptionButton4: col = 37
 End Select
 For Each Dn In Rng
        If Dn = nameBox1 Then
            For Ac = 1 To 31 ' Change to 31
           If Weekday(Cells(4, Ac + 2), vbMonday) < 6 Then
                If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then
                If Dn.Offset(, Ac).Value <> "" Then
                   Dn.Offset(, Ac).Interior.ColorIndex = col
                End If
                End If
                End If
            Next Ac
        End If
    Next Dn
  Unload Me
End Sub

 

[Toonies]

Hi Wigi

your a "STAR"

it worked a treat

I had been trying to figure this one out for a few days

Many thanks again

Toonies