Highlighting fields based on a dropdown in Excel 2003

[Calab]

Hi!

I have a spreadsheet in Excel 2003 that documents some processes that we
follow.

*1 specifies a number of different processes that we do.

A* specifies the different job types that we do.

B2:M11 contains some combination of four charcters ("1234") specifying
different technologies, based on whether or not the process in (A:*) applies
to that technology. For example, if the process in A2 only applies to
technology 2 and 4, B2 will contain "24"

I'd like to add a dropdown to the sheet containing the four technologies.
When a user makes a selection via the dropdown box, I would like to
highlight any field on the form containing characters related to that
technology.

An example:

Job A2 uses processes B2 "12" , C2 "124" , F2 "24"
Job A3 uses processes C3 "123", D3 "4", G3 "12"
Job A4 uses processes C4 "24", D4 "1", E4 "2"
....and the user selects technology 2 from the dropdown.
I want the following cells to be highlighted: B2, C2, F2, C3, G3, C4, E4

Is this possible? If so, how?

 

[Tom Hutchins]

Assuming your dropdown list is in cell A1:
- select all cells
- select Conditional Formatting from the Format menu
- change 'Cell Value Is' to 'Formula Is'
- in the text box enter =(FIND($A$1,A1))>0
- click the Format button
- select the options you want to highlight the cells
- click OK until all the dialogs are closed

If your dropdown list is in a different cell, change $A$1 to that cell (keep
the dollar signs so it remains an absolute reference).

Hope this helps,

Hutch

 

[Calab]

Assuming your dropdown list is in cell A1:
- select all cells
- select Conditional Formatting from the Format menu
- change 'Cell Value Is' to 'Formula Is'
- in the text box enter =(FIND($A$1,A1))>0
- click the Format button
- select the options you want to highlight the cells
- click OK until all the dialogs are closed

If your dropdown list is in a different cell, change $A$1 to that cell
(keep
the dollar signs so it remains an absolute reference).

That works great... but I just discovered one more thing we need that I
can't figure out... I also need to check for a literal value as well.

=(FIND($A$1,A1))>0 OR (FIND("*",A1))>0

Which doesn't work.

: (

 

[Roger Govier]

Hi Tom


Because the "*" is a wildcard, if you want to search for the "*" itself, you
need to prefix it with the tilde "~"
You also have the syntax for the OR function incorrect.
=OR(FIND($A$1,A1)>0,FIND("~*",A1)>0)

 

[Tom Hutchins]

Thanks, Roger. Actually, that was the OP's formula, not mine. When I tried
your corrected formula (Excel 2003), it didn't work. This version seems to
work reliably for me:

=OR(IF(ISERROR(FIND($A$1,A1)),0,FIND($A$1,A1))>0,IF(ISERROR(FIND("*",A1)),0,FIND("*",A1))>0)

Hutch