MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Want to create a button to highlight rows


Posted by Greg on August 17, 2001 7:38 AM

I'm not even sure if this is possible. I want to create a button with a yes/no switch that asks If I want to highlight the non priced rows. If there is a ??? in any row in column G then the security is unpriced. In that case I would like all of that row to be highlighted in red. I then would like it so if I clicked no on the yes/no toggle that it would un highlight the high lit ones. Can this be done? thanks a ton.

-Greg

(PS also, if its possible, can it be made so that if I change the ??? to a number after it is highlighted that If I click the burron again and say yes this one not be one of the highlighted? Very much appreciated)


Posted by lenze on August 17, 2001 8:52 AM

Interesting Question. One way that comes to mind would be to use a control such as a check box which returns TRUE/FALSE. Then use conditional Formatting to test the control in condition 1. If control is TRUE, don't format. If the control is False, go to 2nd condition which would be to highlight row if Col G is ??? (Non numberic?)

Posted by greg on August 17, 2001 9:18 AM

Trouble actually Programming it.

I think I have the basic algorythm down on how I would like to approach this. My problem is that I'm essentially ignorant to the macro vbasic language. Anyone know how to actually program this or can someone give me examples that maybe I could follow. Thanks.

-Greg

Posted by Damon Ostrander on August 17, 2001 9:49 AM

Hi Greg,

Here's some code that does what I believe you want. Just assign this macro to your button.

Happy computing.

Damon

Dim Hilighted As Boolean

Sub Hilight_Nonpriced_Rows()

Dim iRow As Long
Dim LastRow As Long

LastRow = [G65536].End(xlUp).Row

If Hilighted Then
For iRow = 1 To LastRow
If Cells(iRow, 7).Interior.Color = RGB(255, 0, 0) Then
Cells(iRow, 7).EntireRow.Interior.ColorIndex = 0
End If
Next iRow
Else
For iRow = 1 To LastRow
If Cells(iRow, 7).Value = "???" Then
Cells(iRow, 7).EntireRow.Interior.Color = RGB(255, 0, 0)
End If
Next iRow
End If

Hilighted = Not Hilighted 'toggle hilighted value

End Sub

Posted by greg on August 17, 2001 12:39 PM

Damon,

Great. Works perfectly thank you.

-Greg

Hi Greg, Here's some code that does what I believe you want. Just assign this macro to your button. Happy computing. Damon Dim Hilighted As Boolean Sub Hilight_Nonpriced_Rows() Dim iRow As Long LastRow = [G65536].End(xlUp).Row If Hilighted Then For iRow = 1 To LastRow If Cells(iRow, 7).Interior.Color = RGB(255, 0, 0) Then Cells(iRow, 7).EntireRow.Interior.ColorIndex = 0 End If Next iRow Else For iRow = 1 To LastRow If Cells(iRow, 7).Value = "???" Then Cells(iRow, 7).EntireRow.Interior.Color = RGB(255, 0, 0) End If Next iRow End If Hilighted = Not Hilighted 'toggle hilighted value