MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Is this possible with Conditional Format ?


Posted by Bruno on December 14, 2001 3:26 AM

The cells of column F contains the columns that need some extra attention of the user. I want to use cond.format to highlight these columns :

cell F2 : "HLM" means that cells H2+L2+M2 need attention
cell F3 : "GHKN" means that cells G3+H3+K3+N3 need attention
ect.

column() returns the column number (ex. 8 for column H),
when I add 64 to this number I become the ASCII code of that column (ex. H)

Which code do I have to use in the cond.format for the cells in range F2:Pxx ?

Any idea's ?


Posted by Aladin Akyurek on December 14, 2001 3:41 AM

Bruno --

Activate H2.
Activate Format|Conditional Formatting.
Choose Formula Is for Condition 1.
Enter as formula:

=ISNUMBER(SEARCH("H",$F$2))

Activate Format.
Do the desired formatting.
Give OK as many as needed to leave Conditional Formatting.

Repeat the same logic for the rest of the cells that you want to highlight.

Aladin

===========

Posted by Bruno on December 14, 2001 3:50 AM

Aladin,
Can I replace the "H" with some code ? I mean : is there a way to use the same code for different columns F:P ?

Bruno

Posted by Aladin Akyurek on December 14, 2001 4:19 AM

Bruno --

I believe I now understand what you're up to.

Use the following formula instead for the range of interest in row 2:

=ISNUMBER(SEARCH(LEFT(ADDRESS(ROW(),COLUMN(),4)),$F$2))

The other row(s) I leave to you.

Cheers.

Aladin

===============

Posted by Bruno on December 14, 2001 5:08 AM

my solution :

Aladin, I've used the following code

Flemish :
=VIND.SPEC(TEKEN(64 +KOLOM());$F2)>0

VIND.SPEC = find
TEKEN = character
KOLOM = column

Works also and not so complex...

Bruno
=====

Posted by Aladin Akyurek on December 14, 2001 6:30 AM

Great. [NT]

I've used the following code