rutgermons
asked on
highlight entire selected range value row
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
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
The combobox is in a form or on a sheet?
ASKER
on a sheet
here is the code
Private Sub ComboBox1_gotfocus()
Dim r As Long
r = Sheet1.Range("A1").Offset( Sheet1.Row s.Count - 2, 0).End(xlUp).Row
With ComboBox1
.ColumnCount = 2
.List = Range("A2:B" & r).Value
End With
End Sub
here is the code
Private Sub ComboBox1_gotfocus()
Dim r As Long
r = Sheet1.Range("A1").Offset(
With ComboBox1
.ColumnCount = 2
.List = Range("A2:B" & r).Value
End With
End Sub
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.L istIndex, 1)
Sheet1.Range("StartCell"). Offset(Ite mCode, 0).Interior.ColorIndex = 36
Sheet1.Range("StartCell"). Offset(Ite mCode, 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)
Dim ItemPosition As Long
Private Sub ComboBox1_Click()
ItemCode = ComboBox1.List(ComboBox1.L
Sheet1.Range("StartCell").
Sheet1.Range("StartCell").
End Sub
Change the color as you wish. Yo can also use RGB and the command becomes .Interior.Color = RGB(200,160,35)
Sorry the Dim is: Dim ItemCode as Double
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
samrad
can u send me the vba code pls?
can u send me the vba code pls?
ASKER
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
u declare
Dim ItemPosition As Long
but dont reference it, where does this fit into my combobox code I specified?
all help will do
ASKER
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
opted the CF format instead and it works smoothly
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