I tried this. Still not working. Any other suggestions?


"Dave Peterson" wrote:

You have to change this line:
With Sheets("Sheet1")
to work with the name you see in the worksheet tab in excel:
With Sheets("my worksheet name here")




MelB wrote:

Thanks for the help.

Actually, I did mean equals "N/A" as in Not Applicable.

I used your code and got the following:

Runtime Error 9:
Subscript out of range.
The debugger highlights that line with sheets (Sheet 1)

Also, I should elaborate that this needs to work for the entire sheet.
Whereever "N/A" appears the background color needs to be copied from the cell
adjacent and to the left of it.

Again, thanks for the help.

"OssieMac" wrote:

Hi,

I hope that I have interpreted correctly. You said contains "N/A", not
equals "N/A" meaning that it could be anywhere in a longer string. Also you
have not said that it contains the error #N/A.

Therefore macro is for what I understand although it will also work if
equals "N/A" but need a modification if it is #N/A due to a formula error.
(See below macro code)

Sub Copy_Inter_Color()

Dim rngB As Range
Dim c As Range

'Assign range of cells in column B with values to a variable
With Sheets("Sheet1")
Set rngB = .Range(.Cells(1, "B"), _
.Cells(.Rows.Count, "B").End(xlUp))
End With

For Each c In rngB
If InStr(1, c, "N/A") 0 Then
c.Interior.Color = c.Offset(0, -1).Interior.Color
End If
Next c

End Sub



Replace the If test with the following line for #N/A error
'If WorksheetFunction.IsNA(c) Then


--
Regards,

OssieMac


"MelB" wrote:

I need a macro that does the following:

If a cell contains the text "N/A" I need the background color of the cell
adjacent and to the left of it, copied and pasted into the cell with "N/A"

So, B1 has "N/A". I need the background color from A1 copied into cell B1,
leaving the text "N/A" intact.

If B2 has "N/A" I need the background or fill color from cell A2 copied into
B2.

In all cases the fill color is just a solid color. I need no other
formatting or content copied, just the fill color.


--

Dave Peterson