condition formula performing actions on different columns

[niukvba]

Hi,
I have the following problem. Please see the attached file.
C column contains some vlookup formula. for some cells it founds the value, and for other it shows N/A.
I need the followng formula on D2 (and the rest of the column)- if C2 is N/A, don't do anything, otherwise change the value in B2 to "green".

I can't use the IF, because it does not allow to refer to a different column.

Will appreciate your help.

Thanks

[FlameRetired]

Copy and past this formula in B2 and fill down?

Please Login or Register  to view this content.

Is this what you mean?

[niukvba]

Thanks, but not exaclty, as I need B2 to stay with the origianl value if it's false. You have put "blue", but this is just an exmaple, and there might be different values there, not only blue. So is it's not NA, I need the B2 to be not touched.

[AlanY]

you can use conditional formatting

test.xlsx

[bmouse]

You can try this macro, however it requires a bonus column (column D in this case) with this formula dragged down =ISNA(C2)
Adjust EndNumber value depending on the lenghth of your list

Please Login or Register  to view this content.

[FlameRetired]

When you say B2 to be untouched..........if the value remained the same but conditional formatting as AlanY has suggested could coerce B2 to display "green" without touching it's underlying value,....would that work?

[niukvba]

Thanks.
What if use the same macro each time for different number of raws. What would I put in the EndNumber?

[niukvba]

conditional formating wont work. I need for some cells to actually change the value (as I am going to do something with it) and some cell to stay with the value which was there before.

[bmouse]

Thanks.
What if use the same macro each time for different number of raws. What would I put in the EndNumber?

The EndNumber is used to determine how many rows the macro has to process. In this case, your example had 8 rows of data, so EndNumber=8. If your data range has 500 rows, EndNumber=500, otherwise the macro will process only a part of the data range.