Conditional Formatting in Excel 2007 based on Text in another column

[hsshah]

Hello Everyone,

I have a report that has to be sent weekly, then 4 weekly reports convert into a monthly report and then 12 monthly reports convert into 1 yearly report.
Now the problem is that I need to do conditional formatting on a single column based on the Text values that another column contains. Here's the pic and i'll explain it in detail after this

Now in Column N i have detail of the site whether it is microwave or VSAT site. Now the formatting is to be based on this column as follows:

• If column N contains the acronym "VSAT" whether it is VSAT+MW Site or VSAT Site it should color light red the corresponding entry in column D.
• If the acronym "VSAT" is not present then it should color light orange the corresponding entry in column D.

Here's what I did.

• I went to conditional formatting in the Excel 2007 ribbon.
• I chose: New Rule
• I chose: Use a formula to determine which cells to format.
• Then in format values where this formula is true I wrote: =N3="VSAT Site"
• I then set the formatting needed
• Then in Manage rules I chose applies to as : =$D$3:$D$28
• Then I click apply and OK and nothing happened.

Help needed as to what am I doing wrong? This would lessen the amount of work which I have to do substantially.

Thanks in advance

 

[mole999]

i would do the CF in two passes

in N2

Cell Value Contains 'VSAT' Applies to =$N$2:$N$17

in D2

Formula: = =$N$2="VSAT" Applies to =$D$2:$D$17
Formula: = =$N$2<>"VSAT" Applies to =$D$2:$D$17 should provide the orange trigger

extend as necessary

 

[hsshah]

i would do the CF in two passes

in N2

Cell Value Contains 'VSAT' Applies to =$N$2:$N$17

in D2

Formula: = =$N$2="VSAT" Applies to =$D$2:$D$17
Formula: = =$N$2<>"VSAT" Applies to =$D$2:$D$17 should provide the orange trigger

extend as necessary

Hey man thanks a lot but...

I hope i did it alright? Pictures attached to ensure.
But still no effect. No change at all.

and

Kindly help.

 

[mole999]

in your first picture you don't have any format set, so even if working there will be no visible change

I only recorded the action and dragged down to test it (what you have there looks right to me), any other formats on those cells that might take priority ?

maybe just try it on a new sheet and test

 

[Peter_SSs]

Kindly help.

That would be much easier if we had easy access to some sample data. The problem with the provided sheet image is that the data cannot be copied from that to a sheet to test.

Any chance you could use one of the methods shown in my signature block below to provide the sample data directly in your post? Then people can copy it to test with.

 

[hsshah]

g

 

[hsshah]

That would be much easier if we had easy access to some sample data. The problem with the provided sheet image is that the data cannot be copied from that to a sheet to test.

Here is the link to sample data

TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[Peter_SSs]

Firstly, you'll generally get more helpers if you paste sample data directly into your post as I suggested. many helpers will not download from third-party sites or their security environment does not allow it.

Secondly, your columns seem to have changes as the "VSAT Site/ Microwave Site" column now appears to be M, not N as first stated.

Thirdly, since everything will be red or orange, you actually do not need 2 CF rules. You can manually colour the whole column orange for example and just have a CF rule to change the relevant cells to red.
Having said that, I have provided 2 rules in case you do want/need them for some reason.


Anyway, try this.

1. Remove all existing Conditional Formatting from column D

2. Select from D3:D??

3. Apply the CF rules as shown

Excel Workbook
	D	M
3	SKU2326	MW_2nd E1
4	Transit_2nd E1	Transit 2nd E1
5	Transit_2nd E1	Transit 2nd E1
6	Transit_2nd E1	Transit 2nd E1
7	Link Down	VSAT+MW Site
8	Link Down	VSAT Site
9	Link Down	VSAT Site
10	Transit_2nd E1	Transit 2nd E1
11	GLT2311	MW_8th E1
12	Transit_2nd E1	Transit 2nd E1
13	Transit_2nd E1	Transit 2nd E1
14	Link Down	VSAT Site
15	BSM2503	MW_1st E1
16	Transit_2nd E1	Transit 2nd E1
17	Link Down	VSAT+MW Site
18	Transit_2nd E1	Transit 2nd E1
19	Link Down	VSAT+MW Site
20	Transit_2nd E1	Transit 2nd E1
21	GLT2315	MW_5th E1
22	Link Down	VSAT Site
23	BSM2503	MW_1st E1
24	Transit_2nd E1	Transit 2nd E1
25	BHR2227	MW_2nd E1
26	Transit_2nd E1	Transit 2nd E1
27	SKU2326	MW_2nd E1
28	Link Down	VSAT+MW Site
Sheet2 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3	1. / Formula is =SEARCH("vsat",M3)	Abc
D3	2. / Formula is =ISERR(SEARCH("vsat",M3))	Abc

 

[hsshah]

Firstly, you'll generally get more helpers if you paste sample data directly into your post as I suggested. many helpers will not download from third-party sites or their security environment does not allow it.

Secondly, your columns seem to have changes as the "VSAT Site/ Microwave Site" column now appears to be M, not N as first stated.

Thirdly, since everything will be red or orange, you actually do not need 2 CF rules. You can manually colour the whole column orange for example and just have a CF rule to change the relevant cells to red.
Having said that, I have provided 2 rules in case you do want/need them for some reason.


Anyway, try this.

1. Remove all existing Conditional Formatting from column D

2. Select from D3:D??

3. Apply the CF rules as shown

Sheet2 (2)

	D	M

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:116px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #ffc000, align: center"]SKU2326[/TD]
[TD="align: center"]MW_2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #ff0000, align: center"]Link Down[/TD]
[TD="align: center"]VSAT+MW Site[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #ff0000, align: center"]Link Down[/TD]
[TD="align: center"]VSAT Site[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #ff0000, align: center"]Link Down[/TD]
[TD="align: center"]VSAT Site[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #ffc000, align: center"]GLT2311[/TD]
[TD="align: center"]MW_8th E1[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="bgcolor: #ff0000, align: center"]Link Down[/TD]
[TD="align: center"]VSAT Site[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="bgcolor: #ffc000, align: center"]BSM2503[/TD]
[TD="align: center"]MW_1st E1[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="bgcolor: #ff0000, align: center"]Link Down[/TD]
[TD="align: center"]VSAT+MW Site[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="bgcolor: #ff0000, align: center"]Link Down[/TD]
[TD="align: center"]VSAT+MW Site[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="bgcolor: #ffc000, align: center"]GLT2315[/TD]
[TD="align: center"]MW_5th E1[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="bgcolor: #ff0000, align: center"]Link Down[/TD]
[TD="align: center"]VSAT Site[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="bgcolor: #ffc000, align: center"]BSM2503[/TD]
[TD="align: center"]MW_1st E1[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="bgcolor: #ffc000, align: center"]BHR2227[/TD]
[TD="align: center"]MW_2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="bgcolor: #ffc000, align: center"]Transit_2nd E1[/TD]
[TD="align: center"]Transit 2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="bgcolor: #ffc000, align: center"]SKU2326[/TD]
[TD="align: center"]MW_2nd E1[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="bgcolor: #ff0000, align: center"]Link Down[/TD]
[TD="align: center"]VSAT+MW Site[/TD]

</tbody>

Conditional formatting

Cell Nr.: / Condition Format D3 1. / Formula is =SEARCH("vsat",M3) <tbody> [TD="bgcolor: #ff0000"]Abc

<tbody>

D3	2. / Formula is =ISERR(SEARCH("vsat",M3))

[TD="bgcolor: #ffc000"]Abc[/TD]

</tbody>

[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks a lot Peter_SSs I just wanted to ask one more thing what if I wanted to apply, or use would be the proper word, these formulas on only Non-Blank entries. Blank entries should remain white or as originally they were.

Thanks again Peter_SSs as it was extremely helpful of you.

 

[Peter_SSs]

.. on only Non-Blank entries.

=AND(SEARCH("vsat",M3),D3<>"")

and a similar change to the other formula.


Also, best not to quote peoples whole message if you are not referring to it all, it fills up the board needlessly and makes the thread a bit harder to read/navigate.