Shading cells based on Other Sheet Value?

[RustyR]

I have a workbook that has dropdowns with Yes and No answers.

Is there a way to select a range of cells in another worksheet (some of
which may be merged) and Format those cells with a patter based on a Yes or
No answer in another worksheet?

I ran a macro to do it and got this but I don't know what to do with it or
how to correlate it with say, Requestor!B10 (example).

Sub ShadeCell()
Range("B15:V18").Select
With Selection.Interior
..ColorIndex = 2
..Pattern = xlGray25
..PatternColorIndex = xlAutomatic
End With
End Sub

Thank you in advance.
Rusty

 

[Doug Glancy]

Rusty,

You can use Conditional Formatting from the Formatting menu.

Experiment first using CF where the cells to be patterned and the "yes no"
cell are in the same sheet. Having the source in another sheet requires
that you name the "yes no" cell and then refer to the name in the
Conditional Formatting dialog box. For example, with a cell named
"yes_or_no" in Sheet 1, the formula in the Conditional Formats dialog box
for a group of cells in Sheet 2 is:

=yes_or_no="yes"

hth,

Doug Glancy