Excel: Fill range of cells with "NA" if another cell ="xHigh"

[SPYatwork]

I need a way to populate cells in W4:BH4 with "NA" if Q4 = "xhigh".
(this needs to work on all row in sheet not just row4)
Any help appreciated!

 

[SkipVought]

So you want to return a STRING "NA" rather than the NA() error value?

And do you mean that if Q4 contains "xhigh" then ALL cells in the stated range are "NA"?

Why don't you use a simple spreadsheet formula? Excel 101.

Please answer each of these questions completely.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SPYatwork]

Yes I want to add string "NA" not an error value

Yes, if q4 ="xHigh" all cells in that row between Cols W4:BH4 should have the value string "NA". Must apply to all rows in sheet i.e if q5 - "xHigh" then w5:bh5 should be "NA"

I cant use a fomula because the cells in W:BH may have existing data that needs to be overwritten.

Thanks

 

[SkipVought]

I cant use a fomula because the cells in W:BH may have existing data that needs to be overwritten.

That you cannot use a formula is NOT TRUE!

You can 1) ENTER the formula in W4, and 2) COPY W4, then 3) PASTE in W4:BH4.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

You can still use a formula.

Put the data which currently goes in W:BH onto another sheet (or somewhere else on this sheet) and use a conditional to display it on this sheet, or the NA value as determined by the value in column Q.

Lets say the sheet you are working with is Sheet1. Move the data in W:BH onto sheet 2 in the same columns.

Then, in cell W1 (or whichever row you want it to start at) put the formula = if($Q4="xHigh", "NA",sheet2!W1)

Then copy this across to all the columns between W and BH, and down as many rows as you need.

Tony

 

[combo]

If it is possible to mark cells in other way, you could use conditional formatting:
1) for cells W4:BH4 set CF condition (formula): =($Q4="xHigh")
2) set the same colours of font and background in CF, say gray,
3) copy formats down/up.

combo

 

[SPYatwork]

Thanks for all replies.
The conditional format option works but is not viable as I need the text "NA" in the cells.

The option of refering to data on another sheet is not really viable either as this is a constantly updating document.

Pasting formulas into range W:BH would overwrite existing data so this won't work.

What I really need is some code to look at each row for column q & if it shows "xHigh" then cycle thru each cell in W:BH for that row & enter value of "NA

 

[SkipVought]

sub DoNA()
dim r as range

for each r in range(cells(4, "W"), cells(4, "BH"))
   if cells(4, "Q").value = "xhigh" then
      r.value = "NA"
   end if
next
end sub

past this sub in a MODULE and RUN.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SPYatwork]

Thanks Skip,
I need this to run on all rows checking q1,q2,q3 etc and filling in W1:BH1, W2:BH2, W3:BH3 etc and also to trigger automatically ( maybe worksheet on change event?).
Thanks again

 

[SkipVought]

sub DoNA()
dim c as range, r as range

for each r in range(cells(1, "Q"), cells(1, "Q").end(xldown))
     if r.value = "xhigh" then
        range(cells(r.row, "W"), cells(r.row, "BH"))
.value = "NA"
     end if
next
end sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}