We help IT Professionals succeed at work.

highlight entire selected range value row

640 Views
Last Modified: 2013-11-24
Folks

I have a combo box that selects from values in column A

i.e.

A                               B
British Airways         UK
Qantas                      AUS
Lufthansa                 Germany

If I select from the list in the combo box, how can my macro then highlight the entire selected row in the excel sheet itself based on my choice

so if for example I select Qanta from colum a, both qantas and AUS will be highlighted in
Red
Comment
Watch Question

Commented:
The combobox is in a form or on a sheet?

Author

Commented:
on a sheet

here is the code

Private Sub ComboBox1_gotfocus()
Dim r As Long

r = Sheet1.Range("A1").Offset(Sheet1.Rows.Count - 2, 0).End(xlUp).Row
    With ComboBox1
        .ColumnCount = 2
        .List = Range("A2:B" & r).Value
    End With

End Sub

Commented:
I have named the upper left cell of the range as StartCell, then add this code:

Dim ItemPosition As Long

Private Sub ComboBox1_Click()
ItemCode = ComboBox1.List(ComboBox1.ListIndex, 1)
Sheet1.Range("StartCell").Offset(ItemCode, 0).Interior.ColorIndex = 36
Sheet1.Range("StartCell").Offset(ItemCode, 1).Interior.ColorIndex = 36
End Sub

Change the color as you wish. Yo can also use RGB and the command becomes .Interior.Color = RGB(200,160,35)

Commented:
Sorry the Dim is: Dim ItemCode as Double
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
samrad

can u send me the vba code pls?

Author

Commented:
yuppudu,Im struggling wtih ur code,

u declare

Dim ItemPosition As Long



but dont reference it, where does this fit into my combobox code I specified?

all help will do

Author

Commented:
thanks for the solution, this wasnt a direct code fix in the macro but I used this sample and
opted the CF format instead and it works smoothly

Commented:
rutgermons, I've added a comment right after the code posting saying that the Ddim was wrong and it shoud've been: Dim ItemCode as Double
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.