Conditional Formatting With color variation output

[Ali Atwi]

I have three columns that contain data.
Column A, Column B, and Column C.
Now if data for a certain column is invalid, it will contain text as follows: "N/A".
I want to color code the variation existing for the the three columns.
For example:
If on row 3, 3A contains data, 3B contains data, but 3C contains N/A, i want the color of all three cells to be blue (not text color but fill color).
If on row 4, 4A contains N/A, 4B contains N/A, and 4C contains data, I want the color of all three cells to be yellow.
and the rest of the variations as well (5A contains Data, 5B contains N/A, and 5C contains data then all three green etc... etc..)
Of course logically the condition is strictly related to the N/A, and the rest of the data can contain text and numbers if it is not N/A.
Can the color coding be placed in RGB format as well? or can I create a cell with the fill color desired as a reference (in other words a legend that I can refer to when color coding the sheet)?

Thank you, I hope I was clear enough.

 

[miss_ell]

Hello,

Highlight the data in the three columns, then add the conditional formatting details:-

Condition 1 =$A3="N/A" then format yellow
Condition 2 =$B3="N/A" then format green
Condition 3 =$C3="N/A" then format blue


I hope this helps.

 

[Ali Atwi]

I didn't know how to input the conditions. I'm using Excel 2007 on windows 7, sorry I didn't say that earlier.
I want the color to be for all three cells on the same row.

Budget

1 FC

2 FC

Budget

1 FC

2 FC

Present in all 3

Country

Customer

Ma

Delivery Time

Ma2

Delivery Time3

Ma3

Delivery Time2

Present in Budget Only

X

X

United States

Customer 1

500

Jul-13

123

May-13

123

May-13

Present in Budget and 1 FC

X

United Kingdom

Customer 2

35

Sep-13

3424

Mar-13

3424

Jul-13

Present in 1 FC and 2 FC

X

Argentina

Customer 3

N/A

432

Jun-13

432

May-13

Present in 1 FC only

X

X

Lima

Customer 4

N/A

234

Jun-13

234

Aug-13

Present in 2 FC only

X

X

Bla bla

Customer 5

N/A

N/A

123

Oct-13

Total

XX

Customer 6

N/A

N/A

3424

Oct-13

United States

Customer 7

N/A

43

Jun-13

432

N/A

United Kingdom

Customer 8

N/A

123

Jun-13

234

N/A

Argentina

Customer 9

123

Oct-13

N/A

123

N/A

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

For instance in this case for customer 1, the July-13, May-13, and May-13 would be of lets say the color green, and these three relative cells would be of the same color for customer 2, however not for Customer 9 because of the 2 N/A present.

 

[Ali Atwi]

I solved it. Thank you anyway. Just reordering the conditions in the Conditional formatting did the trick. I just had to use the =AND(...) as well as the one you posted. Thank you.

 

[Tyron]

Hello All,

I have a similar dilemma. I have a spreadsheet where there are 50 columns of data and 50 rows of data. I would like to have all cells indicating the "#N/A" result turn black so it is easy to pull out of the data. As I am using Excel2013 the conditional formatting procedure seems different. I am also trying to avoid having to alter the formulas manually as 50 * 50 = 2500 formulas to change.

Any help is appreciated.

Ty

 

[Ali Atwi]

Hello All,

I have a similar dilemma. I have a spreadsheet where there are 50 columns of data and 50 rows of data. I would like to have all cells indicating the "#N/A" result turn black so it is easy to pull out of the data. As I am using Excel2013 the conditional formatting procedure seems different. I am also trying to avoid having to alter the formulas manually as 50 * 50 = 2500 formulas to change.

Any help is appreciated.

Ty

I do not know about Excel 2013 and how its conditional formatting might work. but i think you can pace="#N/A" in the rule and the conditino will follow.

if you can send the file or upload a sample and tell me what it is exactly you are trrying to do then i can help you out.

 

[Tyron]

I do not know about Excel 2013 and how its conditional formatting might work. but i think you can pace="#N/A" in the rule and the conditino will follow.

if you can send the file or upload a sample and tell me what it is exactly you are trrying to do then i can help you out.

Hey Ali Atwi,

Thanks. You were right. There was a rule specifically for the #N/A. Thank you for your help. Sorry I didn't get back to you right away. I forgot what website I posted this particular question on.

later

Ty