Excel - Change background color of row based on cell value.

[BobWeston]

Hi Everyone!
I'm a little out of my comfort zone here and could use some help with a VBA Excel issue.

Here's the situation: I have an intranet website that dynamically creates org charts based on our company directory. I'm in the process adding a "download to excel" function that the user can then distribute as necessary. So far, I've been able export the data into an existing template and make some basic formatting changes (i.e. cell borders etc.). Now what I'm trying to do is change the background color for part of the row (only columns A-O) if say the employee's title is, say, Vice President or General Manager as just an example. The actual titles that I'd want to change the background colors for would be listed in a column in a hidden worksheet within the same workbook.

Any suggestions or examples? Anything you could offer would be appreciated.

Thanks,
Bob Weston

 

[euskadi]

OK, so let's say the column containing the title is column Q. Enter cell A1 and do Conditional Formatting (under the formatting menu) and do "formula equals" =($Q2="Vice President&quot and select the appropriate format. You can do up to 3 different formats.

Then Copy cell A1 and paste/special/formats to columns A thru O. That should get you the result you're looking for.

 

[BobWeston]

Euskadi,
One thing I falied to mention in my first post is that I'm trying to accomplish all my formatting via the Autpen macro so that it's transparent to the end user.

Ideally, this list of titles that I would need to change background colors on would exist on sheet2, which I've hidden. That way, it would be fairly easy for me to go in and add/change as necessary.

While I'm proficient in Excel, I've only dabbled with VBA and can only be considered a beginner.

Thinking about the problem from a SQL perspective, I envisioned something like this, only in VBA:

Select Title
From Sheet1
Where Title in (Select Title from Sheet2)

Then you'd loop through the results and make the changes as necessary.

I'm just not sure of how to go about implementing this.

Any help would be appreciated