Solved: Transfer Results of Conditional Formatting in one cell to another

[Eric58132]

Hi everyone, always love this forum: Happy to report that I haven't stopped in nearly as frequently because I don't need assistance as often! Perhaps I should start responding to the inquiries of others, heh heh.

Anyway, my new company uses Excel 2010 (way better than the '03 at my former job), and I was asked to create a utilization dashboard for my team to task out our work.

What I'm trying to do is this: Use the 3-tiered coloring in Excel 2010's conditional formatting to change the color of each employee's weekly scheduled hourly workload, based upon the number of hours they can work in a week (taking the fact that they have holidays/vacation spread throughout the year into account).

Basically, I have a cell that lists each person's hours of scheduled work, and another that lists the total of hours they have available to work. I then have a percentage, based upon scheduled/available.

For example:

I have 10(cell A1) hours of scheduled tasks, and am only available to work for 24(cell A2) hours. The "percentage" cell reports this as 41%(cell A3). If I conditionally format this 41% on a 0-100% scale, with green being near 0%, yellow near 50% and red up near 100%, this would result in the cell being a yellowish tint. My question, is how do I get the yellowish tint from cell A3 to display in cell A1?

[Eric58132]

well, I see this has had a lot of views but no replies. One small bump to see if anyone not watching over the weekend has any suggestions : pray2:

[Aussiebear]

Try recording a macro by carrying out the format painter procedure on the required cells and then post the resulting code here for us to see.

[Eric58132]

the problem was that there is no specific way "use the excel HUD to get the result I needed", because when pasting the colors over, regardless of how they were pasted, the conditional formatting always went along with it. I found a workaround that, while it does not allow me to use the 3-ranged colors I wanted, gets the job done.

With Range("C8")
For i = 1 To 60
On Error GoTo Nextinline:
ColorScale = .Cells(1, i).Value / .Cells(10, i).Value

If ColorScale < 0.5 Then
green = 255
red = ColorScale * 2 * 255
Else
green = (1 - ColorScale) * 2 * 255
red = 255
End If

.Cells(1, i).Interior.Color = RGB(red, green, 0)
Nextinline:
Next i
End With

Thanks!

**EDIT** Did the space to mark the post as resolved move? I can't seem to find it