I need help with a checkbox. I have a form with comboboxes, textboxe
and "one" checkbox. I need the checkbox when checked to highligth th
offset cells with a bright yellow color "colorindex = 6". Any hel
would be appreaciated.

The following is "part" of my code in the form.

Private Sub CommandButton1_Click()
'finds time string in column d
Dim FindString As String
Dim Rng As Range

FindString = ComboBox1.Value
If Trim(FindString) < "" Then
Set Rng = Range("d:d").Find(what:=FindString, _
after:=Range("D" & Rows.Count), _
LookIn:=xlFormulas, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


'If cell next to time string is filled offset is disabled

If Rng.Offset(0, 1).Value = "" Then

'inserts content of text/combobx in sequence based on time strin
location
Rng.Offset(0, 2).Value = TextBox1.Text
Rng.Offset(0, 3).Value = TextBox2.Text
Rng.Offset(0, 6).Value = TextBox3.Text
Rng.Offset(0, 7).Value = ComboBox5.Text
Rng.Offset(0, 8).Value = ComboBox6.Text
Rng.Offset(0, 1).Value = TextBox6.Text
Rng.Offset(0, 0).Value = ComboBox1.Text
Rng.Offset(0, 4).Value = ComboBox3.Text
Rng.Offset(0, 5).Value = ComboBox2.Text
Rng.Offset(0, 9).Value = ComboBox4.Text

This is where I need help with
'Code for highlighting designated range'
If CheckBx1.Value = True Then
Offset range
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Else
offset range.Interior.ColorIndex = xlNone
End If


'As info is added message box pops up
MsgBox "Pickup Added Successfully"

response = MsgBox("Schedule another Pickup?", _
vbYesNo)
'When record is added code clears userform
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
ComboBox5.Text = ""

TextBox6.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox4.Text = ""

ComboBox2.SetFocus
'when "no" is selected it closes form
Else
Unload Me
End If
Else
MsgBox "Time Slot Occupied"
End If

If Not Rng Is Nothing Then Application.Goto Rng, True
End If
End Sub

'cancels userform
Private Sub CommandButton2_Click()
End
End Sub


- Larry -
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Hello,

If CheckBx1.Value = True Then
Rng.Offset(0, 1).resize(1,8).interior.colorindex= 6
....

Hope this is what you mean.
Regards,

Jean-Yves


"nrage21 " wrote in message
...
I need help with a checkbox. I have a form with comboboxes, textboxes
and "one" checkbox. I need the checkbox when checked to highligth the
offset cells with a bright yellow color "colorindex = 6". Any help
would be appreaciated.

The following is "part" of my code in the form.

Private Sub CommandButton1_Click()
'finds time string in column d
Dim FindString As String
Dim Rng As Range

FindString = ComboBox1.Value
If Trim(FindString) < "" Then
Set Rng = Range("d:d").Find(what:=FindString, _
after:=Range("D" & Rows.Count), _
LookIn:=xlFormulas, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


'If cell next to time string is filled offset is disabled

If Rng.Offset(0, 1).Value = "" Then

'inserts content of text/combobx in sequence based on time string
location
Rng.Offset(0, 2).Value = TextBox1.Text
Rng.Offset(0, 3).Value = TextBox2.Text
Rng.Offset(0, 6).Value = TextBox3.Text
Rng.Offset(0, 7).Value = ComboBox5.Text
Rng.Offset(0, 8).Value = ComboBox6.Text
Rng.Offset(0, 1).Value = TextBox6.Text
Rng.Offset(0, 0).Value = ComboBox1.Text
Rng.Offset(0, 4).Value = ComboBox3.Text
Rng.Offset(0, 5).Value = ComboBox2.Text
Rng.Offset(0, 9).Value = ComboBox4.Text

This is where I need help with
'Code for highlighting designated range'
If CheckBx1.Value = True Then
Offset range
With Selection.Interior
ColorIndex = 6
Pattern = xlSolid
End With

Else
offset range.Interior.ColorIndex = xlNone
End If


'As info is added message box pops up
MsgBox "Pickup Added Successfully"

response = MsgBox("Schedule another Pickup?", _
vbYesNo)
'When record is added code clears userform
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
ComboBox5.Text = ""

TextBox6.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox4.Text = ""

ComboBox2.SetFocus
'when "no" is selected it closes form
Else
Unload Me
End If
Else
MsgBox "Time Slot Occupied"
End If

If Not Rng Is Nothing Then Application.Goto Rng, True
End If
End Sub

'cancels userform
Private Sub CommandButton2_Click()
End
End Sub


- Larry -
VBA Amateur


---
Message posted from http://www.ExcelForum.com/

Help please!

- Larry -
VBA Amateu

--
Message posted from http://www.ExcelForum.com

I get run time error '424':
Object required

I don't know if your suggestion would help me. My goal is that when th
user selects checkbox1 then it highlights (in yellow) the cells wher
text was placed by the offset procedure.

I don't know if I make myself clear.??

My code now:

Private Sub CommandButton1_Click()
'finds time string in column d
Dim FindString As String
Dim Rng As Range

FindString = ComboBox1.Value
If Trim(FindString) < "" Then
Set Rng = Range("d:d").Find(what:=FindString, _
after:=Range("D" & Rows.Count), _
LookIn:=xlFormulas, _
lookat:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


'If cell next to time string is filled offset is disabled

If Rng.Offset(0, 1).Value = "" Then

'inserts content of text/combobx in sequence based on time strin
location
Rng.Offset(0, 2).Value = TextBox1.Text
Rng.Offset(0, 3).Value = TextBox2.Text
Rng.Offset(0, 6).Value = TextBox3.Text
Rng.Offset(0, 7).Value = ComboBox5.Text
Rng.Offset(0, 8).Value = ComboBox6.Text
Rng.Offset(0, 1).Value = TextBox6.Text
Rng.Offset(0, 0).Value = ComboBox1.Text
Rng.Offset(0, 4).Value = ComboBox3.Text
Rng.Offset(0, 5).Value = ComboBox2.Text
Rng.Offset(0, 9).Value = ComboBox4.Text

This is where I need help with
'Code for highlighting designated range'

If CheckBx1.Value = True Then
Rng.Offset(0, 1).Resize(1, 8).Interior.ColorIndex = 6
Else
Rng.Offset(0, 1).Resize(1, 8).Interior.ColorIndex
xlNone
End If


'As info is added message box pops up
MsgBox "Pickup Added Successfully"

response = MsgBox("Schedule another Pickup?", _
vbYesNo)
'When record is added code clears userform
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
ComboBox5.Text = ""

TextBox6.Text = ""
ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox4.Text = ""

ComboBox2.SetFocus
'when "no" is selected it closes form
Else
Unload Me
End If
Else
MsgBox "Time Slot Occupied"
End If

If Not Rng Is Nothing Then Application.Goto Rng, True
End If
End Sub

'cancels userform
Private Sub CommandButton2_Click()
End
End Sub


- Larry -
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Thx Jean... I got it working after
I placed an error handler.

- Larry -
VBA Amateu

--
Message posted from http://www.ExcelForum.com