Excel 2007-programming macros that apply to data validation

[Derek Megyesi]

Greetings,

I am new to this newsgroup, so nice to meet everyone!

I have an Excel dilemma that I’d like help with.

I’d like to apply some macro programming logic to a data validation cell
entry, so then when a certain data value is selected in the pull down, the
entire row changes color. Here is the algorithm I wish to follow:
1) Launch Excel 2007
2) Go to Data -> Data Validation Button -> Data Validation in pulldown
selection
3) On the Data Validation Window, select the following:
a. Allow: List (‘ignore blank’ and ‘in-cell dropdown’ are selected/checked)
b. Source: Pass,Fail,Inactive

4) Now here is the fun part…what I want to do is add macro programming
logic, so that the following occurs:
a. If Data Validation.Pass is selected, change the font color of all text in
the current row to “greenâ€
b. If Data Validation.Fail is selected, change the font color of all text in
the current row to “redâ€
c. If Data Validation.Inactive is selected, change the font color of all
text in the current row to “greyâ€

If someone could help me figure this out, I’d be MOST grateful. My email is
(derekmegac)(at)(hotmail)(dot)(com)

 

[Gord Dibben]

You don't really need a macro to do this.

Conditional Formatting would do the trick.

Will assume Excel 2007 since you did not mention a version.

Also assumes DV dropdowns are in Column A

Select the rows from 1 through 10 or whatever range you need.

Home Tab click on Conditional Formatting

New Rule> Rule 1 >Use a formula: =$A1="Pass"

Format the font to Green...........will apply to all cells in the row.

New Rule Same formula as Rule 1 with "Failed" in red font

New Rule Same formula as Rule 1 with "Inactive" in grey font

Note the $ sign in $A1 which locks the column A but increments the row
number.


Gord Dibben MS Excel MVP