Copy the cell fill color to the adjacent call, VBA code HELP, "object required" error

[lmuller92]

Hello, i use excell 2010

My conditional formatting so far will color code names e.g. all cells that say mike have a blue fill color.... no problem that was easy.

But i need the cells to the left of my names column to have the same fill color, e.g. cell f2 is blue and says mike, and i want e2 to recognize that the cell is blue in the adjacent cell and fill its self with the same color. e2 will have a calculated % value.

here is the code i have so far but i keep getting object required, any advise?!

Sub copyValuesAndFormats()
Dim intRow As Integer
Dim rngCopy As Range
Dim rngPaste As Range




'Loop from Rows 1 through 35
For intRow = 1 To 35


'Set the copy and paste range
'CHANGE THE SHEET TO MATCH YOURS
Set rngCopy = DataInColumn.Range("F" & intRow + 1)
Set rngPaste = DataInColumn.Range("E" & intRow + 1)


'Test to see if rows have a value
If rngCopy.Text <> "" Then


'Since it has a value, copy the value and color
rngPaste.Interior.Color = rngCopy.Interior.Color


'If you wish to copy the color of the font as well, uncomment the next line
'rngPaste.Font.Color = rngCopy.Font.Color
End If
Next intRow
End Sub

 

[lmuller92]

I really appreciate the quick responses, but now i am getting "compile error: expected: end of statement" referring to the . after the sheet name

Set rngCopy = ("DataInColumn").Range("F" & intRow + 1)
Set rngPaste = ("DataInColumn").Range("E" & intRow + 1)
............................................^..its that period that is highlighted with the error

Never mind i saw why i had the compile error, but nothing happens when i run the macro

 

[MARK858]

You are missing the word Sheets as in the code I posted.

 

[lmuller92]

You are missing the word Sheets as in the code I posted.

thank you i noticed that right after i hit send, thanks for being patient. nothing happens when i run the macro now though

 

[MARK858]

If using 2010 or later because you are using conditional formatting change...

rngPaste.Interior.Color = rngCopy.Interior.Color

to

rngPaste.Interior.Color = rngCopy.FormatConditions(1).Interior.Color

but as Rick has stated you can do this without VBA.

 

[lmuller92]

If using 2010 or later because you are using conditional formatting change...

rngPaste.Interior.Color = rngCopy.Interior.Color

to

rngPaste.Interior.Color = rngCopy.FormatConditions(1).Interior.Color

but as Rick has stated you can do this without VBA.

Thank you it almost work now! what happened when i ran it:

F2 was brown, i ran the macro and E2 remained white but E3-E35 are all brown. so close!

 

[MARK858]

rngCopy.Offset(,-1).Interior.Color = rngCopy.FormatConditions(1).Interior.Color

 

[lmuller92]

rngCopy.Offset(,-1).Interior.Color = rngCopy.FormatConditions(1).Interior.Color

hmm it works but the whole column E becomes brown, i can make it so that only F2 and E2 are the same but the macro wont continue down the sheet, i attchived this by changing

'Loop from Rows 1 through 35For intRow = 2 To 35

to

'Loop from Rows 1 through 35
For intRow = 2 To 2



i need E2 to match the color in F2, E3 to match F3 and so on down the list

here is the entire code updated i also removed the + 1 were they appeared

Sub copyValuesAndFormats()Dim intRow As Integer
Dim rngCopy As Range
Dim rngPaste As Range








'Loop from Rows 1 through 35
For intRow = 2 To 35




'Set the copy and paste range
'CHANGE THE SHEET TO MATCH YOURS
Set rngCopy = Sheets("DataInColumn").Range("F" & intRow)
 Set rngPaste = Sheets("DataInColumn").Range("E" & intRow)




'Test to see if rows have a value
If rngCopy.Text <> "" Then




'Since it has a value, copy the value and color
rngCopy.Offset(, -1).Interior.Color = rngCopy.FormatConditions(1).Interior.Color




'If you wish to copy the color of the font as well, uncomment the next line
'rngPaste.Font.Color = rngCopy.Font.Color
End If
Next intRow
End Sub

 

[MARK858]

Are your NUM! errors formatted as brown?

 

[lmuller92]

Are your NUM! errors formatted as brown?

Yes E2:E35, the majority are #NUM!, and are all brown.

 

[MARK858]

OK, I can see the issue but not easy to test if the condition is being applied.

The easy way around it is to evaluate the condition again (but then you might as well follow Rick's suggestion... notice there is a pattern here).

And wrap your formula in

IFERROR(YOUR FORMULA,"")