excel help require

[sukanta]

I have a set of data like below. Now I want to set conditional formatting like, If A1=AA then C,E,F column will return N/A filled with red color otherwise blank. I want to avoid formula in cell.

[pre]

A...B……….C……..…D……..…E………....F
1 AA…………….N/A……………....…N/A……..N/A
2
3

[/pre]

 

[jason]

conditional formatting can take care of the red coloring, but i think you'll have to have a formula in the cells to return "N/A" or leave it blank.


if it were me, i would have a formula (=if(a1="AA","N/A","") for the cells then just use conditonal formatting to look at the cells with "N/A" to color them red.


there may be other ways, but i'm going out on a limb to say that to acheive both, the other options may involved vba code

 

[Excel_Kid_663]

Conditional formatting can only return a 'format' based on a Boolean 'Condition'. That is, a result that has a TRUE/FALSE answer. Hence -condition-al -format-ing. You can use a conditional formula however it will not return you a value i'm afraid. I don't know anything about VBA to tell you if you can do it any other way, but through the logic of Excel and general spreadsheet capabilitys I'm going to guess it wont, because conditional formating does exactly what it says on the tin. Kind of like Ronseal right?

 

[Luke M]

If there's at least "something" in the cell, even if it's just a space, you can use CF with a number format of:

"N/A"

to force the cell to display the N/A. However, number formatting only gets triggered by the contents of cell, so a truly blank cell won't do anything. Some reason you want to avoid formulas?