Excel Macro to Highlight Row

[Andreew]

Hi,

I would like to set up a right click option to highlight the row i have right clicked on (if that makes sense). I have already set up the right click option and the highlight option but it doesn't work and presents with the error: User-Defined type not defined. Here is the code i am using:

Option Explicit

Private Sub BuildCustomMenu2()

Dim ctrl As CommandBarControl
Dim btn As CommandBarControl
Dim i As Integer


'add a 'popup' control to the cell commandbar (menu)
Set ctrl = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlPopup, Before:=1)
ctrl.Caption = "Highlight"
For i = 50 To 50 'add a few menu items
'add the submenus
Set btn = ctrl.Controls.Add
btn.Caption = "Highlight Yellow" 'give them a name
btn.OnAction = "HighlightYellow" 'the routine called by the control



Next

End Sub

Private Sub DeleteCustomMenu2()

Dim ctrl As CommandBarControl

'go thru all the cell commandbar controls and delete our menu item
For Each ctrl In Application.CommandBars("Cell").Controls
If ctrl.Caption = "Highlight" Then ctrl.Delete

Next

End Sub
Sub Highlight(ByVal Target As Value)

Dim RngRow As Range
Dim RngFinal As Range
Dim Row As Long
Dim Target As Value

'Cells.Interior.ColorIndex = xlNone

Row = Target.Row


Set RngRow = Range("A" & Row, Target)
Set RngFinal = RngRow

RngFinal.Interior.ColorIndex = 6

End Sub

If anyone could please tell me why i am getting this error and how i can make this work i would be very appreciative.

Thanks.

[westconn1]

btn.OnAction = "HighlightYellow" 'the routine called by the control

i do not see any procedure named highlightyellow in the posted code

[Andreew]

Hi i fixed the error but when i try to run the macro I still get the same error: user-defined type not defined do you know why ? this has me very lost :L

[westconn1]

if you are calling the highlight procedure, you need to pass a range for the target parameter, but as i can not see your fixed code, i can not tell

[Andreew]

Hi, sorry the fixed code is:

Option Explicit

Private Sub BuildCustomMenu2()

Dim ctrl As CommandBarControl
Dim btn As CommandBarControl
Dim i As Integer


'add a 'popup' control to the cell commandbar (menu)
Set ctrl = Application.CommandBars("Cell").Controls.Add _
(Type:=msoControlPopup, Before:=1)
ctrl.Caption = "Highlight"
For i = 50 To 50 'add a few menu items
'add the submenus
Set btn = ctrl.Controls.Add
btn.Caption = "Highlight Yellow" 'give them a name
btn.OnAction = "HighlightYellow" 'the routine called by the control



Next

End Sub

Public Sub DeleteCustomMenu2()

Dim ctrl As CommandBarControl

'go thru all the cell commandbar controls and delete our menu item
For Each ctrl In Application.CommandBars("Cell").Controls
If ctrl.Caption = "Highlight" Then ctrl.Delete

Next

End Sub
Sub HighlightYellow(ByVal Target As Value)

Dim RngRow As Range
Dim RngFinal As Range
Dim Row As Long
Dim Target As Value

'Cells.Interior.ColorIndex = xlNone

Row = Target.Row


Set RngRow = Range("A" & Row, Target)
Set RngFinal = RngRow

RngFinal.Interior.ColorIndex = 6

End Sub

still getting that same error :/
thanks (:

[westconn1]

Sub HighlightYellow(ByVal Target As Value)

as i noted above, as this procedure requires a parameter (target) passed to it, it can not be run from a button click, which can not pass a parameter

what is target in this instance?
are you trying to set the row containing the activecell to highlight if so try like

Sub HighlightYellow()
activecell.entirerow.interior.colorindex = 6
end sub

[Andreew]

haha talk about going about things the hard way. Thank you soo much that is perfect. A quick question is it possible to only highlight the row to column D and not all the way along?

once again thanks heaps and thanks for putting up with my ignorance.

andrew

[westconn1]

A quick question is it possible to only highlight the row to column D and not all the way along?

range(cells(activecell.row, 1), cells(activecell.row, 4)).interior.colorindex = 6