Solved

highlight entire selected range value row

Posted on 2013-11-24
9
563 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
0
Comment
Question by:rutgermons
  • 4
  • 4
9 Comments
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
The combobox is in a form or on a sheet?
0
 

Author Comment

by:rutgermons
Comment Utility
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
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
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)
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
Sorry the Dim is: Dim ItemCode as Double
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Accepted Solution

by:
samrad1 earned 500 total points
Comment Utility
Hey!

You can also do this with no VBA and just a CF with a combo box that has a cell link, or VBA that sets a cell value and then lets the CF color the rows. Posting it in case it helps :-)
cf-row-combo.xlsx
0
 

Author Comment

by:rutgermons
Comment Utility
samrad

can u send me the vba code pls?
0
 

Author Comment

by:rutgermons
Comment Utility
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
0
 

Author Closing Comment

by:rutgermons
Comment Utility
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
0
 
LVL 4

Expert Comment

by:yuppydu
Comment Utility
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
EXCEL Addin problem 7 19
Excel Pivot Table with No Summary Fields 7 29
Calculate days between two dates 7 33
Excel 6 13
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now