macro for copying background color

[MelB]

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 A1 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.

 

[Jim May]

Paste this into a standard Module:

Sub tester()
Set Rng = Selection
For Each c In Rng
If c = "N/A" Then
c.Interior.ColorIndex = Range("A1").Interior.ColorIndex
End If
Next c
End Sub

Then before runing Macro - Highlite your cells (Say B1:B10) then run Tester.