Option Button Code (Highlighting Rows)

T

tvh

I have place 8 option buttons on my worksheet from the Control Toolbox and I
am not a VB guy at all. I've searched and played around with the code, but
it's simply not working for me. Here are my delimas:

OptionButton_1
Be the default choice each time the sheet is opened, and remain invisible.
This button is for nothing other than ensuring the other options are not
selected.

OptionButton_2
I have the following formula [="# of scaffold jobs pending review:
"&(COUNTIF(A12:A49,"Yes"))-(COUNTIF(M12:M49,">0"))], in the cell adjacent to
the button which basically returns the number of jobs pending within a
certain range. When I select OptionButton_2 I would like cells A12:G12 to be
highlighted (say light yellow) for the particular rows being counted in the
formula. So, if there are 3 scaffold jobs pending review, I would like those
three rows highlighted.

OptionButton_3
I have the following formula [="# of items ready for scaffold removal:
"&(COUNTIF(AF12:AF49,2))], in the cell adjacent to the button which counts
the cell if a value of 2 is returned. I would like the same range of cells
as above (A12:G12) highlighted light yellow for the particular rows being
counted in the formula when OptionButton_3 is selected.

The remaining buttons will have similar formulas. So, if I could see the
code structure for the above situations, I can try to figure the rest out on
my own.

Thanks for any help...
 
G

G-2008

Instead of using a formula in the Cell, I would use the OptionButton_Click()
event to both count and highlight the qualifying rows (and clear all if
OptionButton1 is selected):

Private Sub OptionButton1_Click()
Sheet1.Range("A12", "G49").Interior.ColorIndex = xlColorIndexNone
Sheet1.Cells(51, 13).Clear
End Sub


Private Sub OptionButton2_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 1).Value = "Yes" And Sheet1.Cells(iRow, 13).Value
0 Then
Click to expand...
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 36
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub


Private Sub OptionButton3_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 32).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 36
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub


HTH - Glen




tvh said:
I have place 8 option buttons on my worksheet from the Control Toolbox and I
am not a VB guy at all. I've searched and played around with the code, but
it's simply not working for me. Here are my delimas:

OptionButton_1
Be the default choice each time the sheet is opened, and remain invisible.
This button is for nothing other than ensuring the other options are not
selected.

OptionButton_2
I have the following formula [="# of scaffold jobs pending review:
"&(COUNTIF(A12:A49,"Yes"))-(COUNTIF(M12:M49,">0"))], in the cell adjacent to
the button which basically returns the number of jobs pending within a
certain range. When I select OptionButton_2 I would like cells A12:G12 to be
highlighted (say light yellow) for the particular rows being counted in the
formula. So, if there are 3 scaffold jobs pending review, I would like those
three rows highlighted.

OptionButton_3
I have the following formula [="# of items ready for scaffold removal:
"&(COUNTIF(AF12:AF49,2))], in the cell adjacent to the button which counts
the cell if a value of 2 is returned. I would like the same range of cells
as above (A12:G12) highlighted light yellow for the particular rows being
counted in the formula when OptionButton_3 is selected.

The remaining buttons will have similar formulas. So, if I could see the
code structure for the above situations, I can try to figure the rest out on
my own.

Thanks for any help...
Click to expand...
 
T

tvh

Works like a charm! Thanks G!

G-2008 said:
Instead of using a formula in the Cell, I would use the OptionButton_Click()
event to both count and highlight the qualifying rows (and clear all if
OptionButton1 is selected):

Private Sub OptionButton1_Click()
Sheet1.Range("A12", "G49").Interior.ColorIndex = xlColorIndexNone
Sheet1.Cells(51, 13).Clear
End Sub


Private Sub OptionButton2_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 1).Value = "Yes" And Sheet1.Cells(iRow, 13).Value
0 Then
Click to expand...
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 36
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub


Private Sub OptionButton3_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 32).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 36
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub


HTH - Glen




tvh said:
I have place 8 option buttons on my worksheet from the Control Toolbox and I
am not a VB guy at all. I've searched and played around with the code, but
it's simply not working for me. Here are my delimas:

OptionButton_1
Be the default choice each time the sheet is opened, and remain invisible.
This button is for nothing other than ensuring the other options are not
selected.

OptionButton_2
I have the following formula [="# of scaffold jobs pending review:
"&(COUNTIF(A12:A49,"Yes"))-(COUNTIF(M12:M49,">0"))], in the cell adjacent to
the button which basically returns the number of jobs pending within a
certain range. When I select OptionButton_2 I would like cells A12:G12 to be
highlighted (say light yellow) for the particular rows being counted in the
formula. So, if there are 3 scaffold jobs pending review, I would like those
three rows highlighted.

OptionButton_3
I have the following formula [="# of items ready for scaffold removal:
"&(COUNTIF(AF12:AF49,2))], in the cell adjacent to the button which counts
the cell if a value of 2 is returned. I would like the same range of cells
as above (A12:G12) highlighted light yellow for the particular rows being
counted in the formula when OptionButton_3 is selected.

The remaining buttons will have similar formulas. So, if I could see the
code structure for the above situations, I can try to figure the rest out on
my own.

Thanks for any help...
Click to expand...
Click to expand...
 
T

tvh

One more little tweek... OK, so I 'massaged' the code that G-2008 provided
and it works absolutely perfectly, except that the rows remain highlighted
even if I select another button. Selecting button one always clears the
highlights.

If I select button 2, the correct rows are highlighted; when I select button
4, I would like button 2's rows cleared and only the rows for button 4 to be
highlighted. I wish I could buy you code guys a beer!! Many thanks!!

Here's what I have so far:

Private Sub OptionButton1_Click()
Sheet1.Range("A12", "G49").Interior.ColorIndex = xlColorIndexNone
Sheet1.Cells(51, 13).Clear
End Sub


Private Sub OptionButton2_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 1).Value = "Yes" And Sheet1.Cells(iRow, 13).Value
= "" Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 22
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub

Private Sub OptionButton3_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 29).Value = 1 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 15
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton4_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 32).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 35
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton5_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 40).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 45
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton6_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 2).Value = "Yes" And Sheet1.Cells(iRow, 18).Value
= "" Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 50
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub

Private Sub OptionButton7_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 30).Value = 1 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 24
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton8_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 31).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 12
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

tvh said:
Works like a charm! Thanks G!

G-2008 said:
Instead of using a formula in the Cell, I would use the OptionButton_Click()
event to both count and highlight the qualifying rows (and clear all if
OptionButton1 is selected):

Private Sub OptionButton1_Click()
Sheet1.Range("A12", "G49").Interior.ColorIndex = xlColorIndexNone
Sheet1.Cells(51, 13).Clear
End Sub


Private Sub OptionButton2_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 1).Value = "Yes" And Sheet1.Cells(iRow, 13).Value
0 Then
Click to expand...
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 36
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub


Private Sub OptionButton3_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 32).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 36
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub


HTH - Glen




tvh said:
I have place 8 option buttons on my worksheet from the Control Toolbox and I
am not a VB guy at all. I've searched and played around with the code, but
it's simply not working for me. Here are my delimas:

OptionButton_1
Be the default choice each time the sheet is opened, and remain invisible.
This button is for nothing other than ensuring the other options are not
selected.

OptionButton_2
I have the following formula [="# of scaffold jobs pending review:
"&(COUNTIF(A12:A49,"Yes"))-(COUNTIF(M12:M49,">0"))], in the cell adjacent to
the button which basically returns the number of jobs pending within a
certain range. When I select OptionButton_2 I would like cells A12:G12 to be
highlighted (say light yellow) for the particular rows being counted in the
formula. So, if there are 3 scaffold jobs pending review, I would like those
three rows highlighted.

OptionButton_3
I have the following formula [="# of items ready for scaffold removal:
"&(COUNTIF(AF12:AF49,2))], in the cell adjacent to the button which counts
the cell if a value of 2 is returned. I would like the same range of cells
as above (A12:G12) highlighted light yellow for the particular rows being
counted in the formula when OptionButton_3 is selected.

The remaining buttons will have similar formulas. So, if I could see the
code structure for the above situations, I can try to figure the rest out on
my own.

Thanks for any help...
Click to expand...
Click to expand...
Click to expand...
 
G

G-2008

Sorry about that, my oversight. Add this line

Call OptionButton1_Click

as the first line in both OptionButton2_Click() and OptionButton3_Click().
So they should both look like:

Private Sub OptionButton2_Click()
Call OptionButton1_Click
...
(remaining code)
...
End Sub

tvh said:
One more little tweek... OK, so I 'massaged' the code that G-2008 provided
and it works absolutely perfectly, except that the rows remain highlighted
even if I select another button. Selecting button one always clears the
highlights.

If I select button 2, the correct rows are highlighted; when I select button
4, I would like button 2's rows cleared and only the rows for button 4 to be
highlighted. I wish I could buy you code guys a beer!! Many thanks!!

Here's what I have so far:

Private Sub OptionButton1_Click()
Sheet1.Range("A12", "G49").Interior.ColorIndex = xlColorIndexNone
Sheet1.Cells(51, 13).Clear
End Sub


Private Sub OptionButton2_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 1).Value = "Yes" And Sheet1.Cells(iRow, 13).Value
= "" Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 22
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub

Private Sub OptionButton3_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 29).Value = 1 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 15
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton4_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 32).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 35
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton5_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 40).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 45
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton6_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 2).Value = "Yes" And Sheet1.Cells(iRow, 18).Value
= "" Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 50
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub

Private Sub OptionButton7_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 30).Value = 1 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 24
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton8_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 31).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 12
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

tvh said:
Works like a charm! Thanks G!

G-2008 said:
Instead of using a formula in the Cell, I would use the OptionButton_Click()
event to both count and highlight the qualifying rows (and clear all if
OptionButton1 is selected):

Private Sub OptionButton1_Click()
Sheet1.Range("A12", "G49").Interior.ColorIndex = xlColorIndexNone
Sheet1.Cells(51, 13).Clear
End Sub


Private Sub OptionButton2_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 1).Value = "Yes" And Sheet1.Cells(iRow, 13).Value
0 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 36
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub


Private Sub OptionButton3_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 32).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 36
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub


HTH - Glen




:

I have place 8 option buttons on my worksheet from the Control Toolbox and I
am not a VB guy at all. I've searched and played around with the code, but
it's simply not working for me. Here are my delimas:

OptionButton_1
Be the default choice each time the sheet is opened, and remain invisible.
This button is for nothing other than ensuring the other options are not
selected.

OptionButton_2
I have the following formula [="# of scaffold jobs pending review:
"&(COUNTIF(A12:A49,"Yes"))-(COUNTIF(M12:M49,">0"))], in the cell adjacent to
the button which basically returns the number of jobs pending within a
certain range. When I select OptionButton_2 I would like cells A12:G12 to be
highlighted (say light yellow) for the particular rows being counted in the
formula. So, if there are 3 scaffold jobs pending review, I would like those
three rows highlighted.

OptionButton_3
I have the following formula [="# of items ready for scaffold removal:
"&(COUNTIF(AF12:AF49,2))], in the cell adjacent to the button which counts
the cell if a value of 2 is returned. I would like the same range of cells
as above (A12:G12) highlighted light yellow for the particular rows being
counted in the formula when OptionButton_3 is selected.

The remaining buttons will have similar formulas. So, if I could see the
code structure for the above situations, I can try to figure the rest out on
my own.

Thanks for any help...
Click to expand...
Click to expand...
Click to expand...
 
T

tvh

Yep...that works!!

Thanks G!

G-2008 said:
Sorry about that, my oversight. Add this line

Call OptionButton1_Click

as the first line in both OptionButton2_Click() and OptionButton3_Click().
So they should both look like:

Private Sub OptionButton2_Click()
Call OptionButton1_Click
..
(remaining code)
..
End Sub

tvh said:
One more little tweek... OK, so I 'massaged' the code that G-2008 provided
and it works absolutely perfectly, except that the rows remain highlighted
even if I select another button. Selecting button one always clears the
highlights.

If I select button 2, the correct rows are highlighted; when I select button
4, I would like button 2's rows cleared and only the rows for button 4 to be
highlighted. I wish I could buy you code guys a beer!! Many thanks!!

Here's what I have so far:

Private Sub OptionButton1_Click()
Sheet1.Range("A12", "G49").Interior.ColorIndex = xlColorIndexNone
Sheet1.Cells(51, 13).Clear
End Sub


Private Sub OptionButton2_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 1).Value = "Yes" And Sheet1.Cells(iRow, 13).Value
= "" Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 22
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub

Private Sub OptionButton3_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 29).Value = 1 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 15
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton4_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 32).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 35
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton5_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 40).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 45
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton6_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 2).Value = "Yes" And Sheet1.Cells(iRow, 18).Value
= "" Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 50
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub

Private Sub OptionButton7_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 30).Value = 1 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 24
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

Private Sub OptionButton8_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 31).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 12
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub

tvh said:
Works like a charm! Thanks G!

:

Instead of using a formula in the Cell, I would use the OptionButton_Click()
event to both count and highlight the qualifying rows (and clear all if
OptionButton1 is selected):

Private Sub OptionButton1_Click()
Sheet1.Range("A12", "G49").Interior.ColorIndex = xlColorIndexNone
Sheet1.Cells(51, 13).Clear
End Sub


Private Sub OptionButton2_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 1).Value = "Yes" And Sheet1.Cells(iRow, 13).Value
0 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 36
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of scaffold jobs pending review: " & iCount
End Sub


Private Sub OptionButton3_Click()
Dim iCount As Integer
Dim iRow As Integer
For iRow = 12 To 49
If Sheet1.Cells(iRow, 32).Value = 2 Then
Sheet1.Range("A" & iRow, "G" & iRow).Interior.ColorIndex = 36
iCount = iCount + 1
End If
Next iRow
Sheet1.Cells(51, 13).Value = "# of items ready for scaffold removal: " &
iCount
End Sub


HTH - Glen




:

I have place 8 option buttons on my worksheet from the Control Toolbox and I
am not a VB guy at all. I've searched and played around with the code, but
it's simply not working for me. Here are my delimas:

OptionButton_1
Be the default choice each time the sheet is opened, and remain invisible.
This button is for nothing other than ensuring the other options are not
selected.

OptionButton_2
I have the following formula [="# of scaffold jobs pending review:
"&(COUNTIF(A12:A49,"Yes"))-(COUNTIF(M12:M49,">0"))], in the cell adjacent to
the button which basically returns the number of jobs pending within a
certain range. When I select OptionButton_2 I would like cells A12:G12 to be
highlighted (say light yellow) for the particular rows being counted in the
formula. So, if there are 3 scaffold jobs pending review, I would like those
three rows highlighted.

OptionButton_3
I have the following formula [="# of items ready for scaffold removal:
"&(COUNTIF(AF12:AF49,2))], in the cell adjacent to the button which counts
the cell if a value of 2 is returned. I would like the same range of cells
as above (A12:G12) highlighted light yellow for the particular rows being
counted in the formula when OptionButton_3 is selected.

The remaining buttons will have similar formulas. So, if I could see the
code structure for the above situations, I can try to figure the rest out on
my own.

Thanks for any help...
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do add another code to a current one? 4
code to hide/unhide rows 1
Convert Conditional Formatting Into VBA Code Button 0
VB Code Highlight Rows Based on Color Index 8
Cut/Paste Certain Rows from Workbook1 to Workbook2 1
Undo Code Not Quite Working 0
Add rows via a button on a protected sheet 3
Is there a formula that will add only highlighted rows? 6

Top