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]

*** fix in tital name[h=2]Copy the cell fill color to the adjacent cell, VBA code HELP, "object required" error[/h]

 

[MARK858]

What sheet is "DataInColumn" as you haven't defined it anywhere?

 

[lmuller92]

It is the name of my work sheet, bad choice of name i know

 

[MARK858]

Are you saying it is the sheets name as it appears on the tab or the sheets codename?

 

[Rick Rothstein]

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.

You do not need VBA code to do this... you can do it all with Conditional Formatting (CF). Remove your current CF rule and select both Columns E and F (if you are using a restricted range row-wise, then select E1:F1 down to the row you want to stop at), then using this formula for your Conditional Formatting rule...

=$F1="Mike"

Note the dollar sign in front of the F column designation.

 

[lmuller92]

Are you saying it is the sheets name as it appears on the tab or the sheets codename?

that is the name i typed into the space on the work sheet tab that usually says "Sheet1".... so yes it is the name that appears on the tab

i am not sure what a sheet codename is?

 

[MARK858]

Then you are using the wrong syntax it should be

Set rngCopy = Sheets("DataInColumn").Range("F" & intRow + 1)
 Set rngPaste = Sheets("DataInColumn").Range("E" & intRow + 1)

The sheets codename is the name that appears outside the brackets in the VBA properties window (see the link below).

Sheet CodeNames: Reference Sheets in Excel Workbooks by Code Name

 

[lmuller92]

so here is an example of what i have done, i have created a sheet where i can change the percentage in column B and then C - F columns are a bunch of off set functions to remove 0's. e.g. so when i make a bar graph it wont put daves name along the axis.

all the names have a certain color from my original conditional format i will use emojiis to denote a cell fill color since i can not do it on this forum. also i can only place 4 emojis so image the others in column A have different emojii (aka back ground cell fill colors) that are all different.

then i have a macro that will make the bar graphs individual bars match the fill color of column E...... but i cant get columb E to copy the cell fill color directly beside it in column F.

one more point is that i will be changing this from, the whole point is so i can punch in a percent in column B and spit out a graph with no 0's

A B C D E F

Species	Percentage	Temporary data (do not touch)	do not touch	Chart data (Do not touch)	do not touch2
mike	50%	50%	2	50%	mike
dave	0%	0%	4	50%	cam
cam	50%	50%	#NUM!	#NUM!	#NUM!
phil	0%	0%	#NUM!	#NUM!	#NUM!
matt	0%	0%	#NUM!	#NUM!	#NUM!
sally	0%	0%	#NUM!	#NUM!	#NUM!

<tbody>
</tbody>

i tried to simplify it in my original question but maybe the full explanation is needed

 

[lmuller92]

Then you are using the wrong syntax it should be

Set rngCopy = Sheets("DataInColumn").Range("F" & intRow + 1)
 Set rngPaste = Sheets("DataInColumn").Range("E" & intRow + 1)

The sheets codename is the name that appears outside the brackets in the VBA properties window (see the link below).

Sheet CodeNames: Reference Sheets in Excel Workbooks by Code Name

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