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]

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

Okay thank you, i appreciate it. i try Ricks suggestion again.

 

[Rick Rothstein]

Okay thank you, i appreciate it. i try Ricks suggestion again.

You may have to adjust the ranges... I am not sure I got them correct from your earlier posts and I am not sure I understand your actual layout correctly. Basically, you would select the two full columns or the two-column range if you have a handle on how many rows need to be Conditionally Formatted and then set the CF formula as you have it now (I think) but make the column references absolute.

 

[lmuller92]

You may have to adjust the ranges... I am not sure I got them correct from your earlier posts and I am not sure I understand your actual layout correctly. Basically, you would select the two full columns or the two-column range if you have a handle on how many rows need to be Conditionally Formatted and then set the CF formula as you have it now (I think) but make the column references absolute.

https://postimg.org/image/hkz45vnin/

Above is a picture of my excel sheet with a picture of what i was trying to enplane earlier. i cant seem to get your formula to work.

I had the color in the conditional formatting turn every "mike" red. i have since deleted that condition and just manually put the fill colors in column A to show you.

I hope this helps you understand what i am trying to accomplish.

Thanks again
Luke