PDA

View Full Version : Copy Cell Color to Another Cell & Changing Graph Color to Match Cell Color



Foxmann250
07-23-2015, 08:55 AM
I'm not too savvy when it comes to VBA so I need a bit of assistance. This one is a bit tricky to explain so I'll do my best.

I am making a heat map of a circle. I have a Donut Chart with a Pie Chart in the center and I am trying to have the color of the graphs match the specific cells they are pulling data from. In this case, these charts pull data from cells C2:I2. I have specific values in these cells so that the charts colors will be sectioned the way I need them to be. But I also need these cells (C2:I2) to pull colors from different cells. Below these cells are temperature readings from every minute for 60 min. I would like to be able to input the minute I want in cell L2 and have cells C2:I2 pull their color from C3:I3, C4:I4, etc. This way, as I input 1-60 into cell L2, the colors of C2:I2 will change matching my color scale conditional formatting for the temperatures, which in turn will change the colors of the sections of the graphs creating a heat map of the circle. I've attached a picture of my document so far.
13983

Any help would be greatly appreciated. Thanks!

p45cal
07-23-2015, 10:43 AM
This is quite do-able and I would be interested in trying to help; a great help from you though would be a file to work with.
If you have difficulty uploading a file because your post-count here is low, then include a link to the file in the cloud. In turn, you might not be able to post links here until your post count is up, in which case, leave off the http: part of the link.
Finally, possibly the most important part, have a read of http://www.excelguru.ca/content.php?184 and take the action necessary at all the sites (including this one) you have cross posted this to.

Foxmann250
07-23-2015, 08:45 PM
13987

Hey Thanks for the info. Here's the excel file and I'll take action on the 2 other forums I was using. :)

p45cal
07-24-2015, 07:01 AM
I want to use something in vba that was introduced in Excel 2010; have you got at least that version?

No response to the above yet so if you do have a version Excel 2010 or above try the attached.
Either adjust the value in L1 or select a cell in the range A3:I63.

Foxmann250
07-24-2015, 11:41 AM
Hey p45cal,

I have 2010 and your attached file works great! This is awesome!

If you have time, could you kind of explain to me how the code works. I'm trying to learn from this as I go :) Thanks!

p45cal
07-24-2015, 12:44 PM
In the sheet's code-module there are 2 event handlers
1.
Private Sub Worksheet_Change(ByVal Target As Range)
blah
End Sub
Whenever a value on the sheet changes the sub blah is called.

2.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A3:I63"), Target) Is Nothing Then Range("L1").Value = Target.Row - 2
End Sub
This fires every time someone changes the selection of the sheet. Target is the range of the new selection.
Intersect(Range("A3:I63"), Target) is a range representing where the Target (the latest selection) and the range A3:I63 overlap. If they don't overlap then the Intersect is Nothing. If they do overlap then the Intersect is Not Nothing then cell L1 is changed to the row number on the sheet of the selection -2. This of course is a change to the sheet, so the Worksheet_Change sub is fired and blah is called.

The sub blah in a standard code-module makes the changes to the charts:
The first line is:
Set myCell = Range("B2").Offset(Range("L1").Value)
This sets a range object (mycell) relative to cell B2 which is above and to the left of your block of numbers. This is so that offsets from this cell B2 can be used. The offset is the value in L1 So if L1 is 2 the offset from B2 is 2 cells below.
You can step through the code one line at a time with F8 on the keyboard. If you do this in blah and run up to and including executing the Set myCell line, you can put in the Immediate pane (Ctrl+G if you can't see it) the command:
myCell.Select
and press Enter. This will select myCell and you can see which cell it is on the sheet.

For i = 1 To 7
...
Next i
This loops 7 times, one loop per cell-to-process/chart-point-to-change. The value of i changes from 1 to 7 in increments of 1 for each loop.
Inside the loop:

Select Case i
Case 1 To 6
do something for the first 6 cells
Case 7
do something else for the 7th
End Select

In the case of the first 6 cells (these all affect Chart 3):

With ActiveSheet.ChartObjects("Chart 3").Chart.SeriesCollection(1).Points(i)
.Interior.Color = myCell.Offset(, i).DisplayFormat.Interior.Color
End With

Now here I put a With … End With but I did this only while developing and shouldn't have left it in; the equivalent single line of code is:

ActiveSheet.ChartObjects("Chart 3").Chart.SeriesCollection(1).Points(i).Interior.Color = myCell.Offset(, i).DisplayFormat.Interior.Color

Here you can see I used i for the point number of the donut chart, and I also used i as the offset from mycell.
The Offset command is really Offset(no. of rows to offset, no. of columns to offset)
Offset with only one argument is the number of rows.
If you only want to offset a number of columns then you use Offset(,i) where the comma signifies that the i is the second (columns) argument. It's the same as Offset(0,i).

So this line says set the colour of point i in the donut chart to the colour of that cell. The reason I asked about the version of xEcel you were using is because .DisplayFormat was introduced in Excel 2010 and is a very easy way of referring to the resultant fomatting due to conditional formatting. Before Excel 2010 it was difficult (and sometimes impossible (because with the newer graded colours used in conditional formatting introduced in Excel 2007 (I think 2007)) to determine the formatting of a conditionally formatted cell.

Once the first 6 cells have been dealt with, i becomes 7 so the second Case statement gets executed:
ActiveSheet.ChartObjects("Chart 4").Chart.SeriesCollection(1).Points(1).Interior.Color = myCell.Offset(, i).DisplayFormat.Interior.Color

Just the same as the first but refers to the only point (point 1) of the pie chart (Chart 4).

Foxmann250
07-25-2015, 01:23 AM
wow! This is great stuff. Some is a bit over my head but someday I'd love to be on your level :) One last question. Let's say I wanted to change where the columns were represented on the graph. If I wanted all the data below T1 to be the pie chart and then all the data below T2 to be bottom left, etc. would that require drastic changes to the code or would it be easier to just move my column data around?

p45cal
07-25-2015, 01:39 AM
If this were for 1, 2 or 3 sheets then it would be easier to move the columns around.
If this were for multiple sheets then changing the code would end up being easier.
Which is it?

Select Case i
Case 2 To 7
ActiveSheet.ChartObjects("Chart 3").Chart.SeriesCollection(1).Points(i - 1).Interior.Color = myCell.Offset(, i).DisplayFormat.Interior.Color
Case 1
ActiveSheet.ChartObjects("Chart 4").Chart.SeriesCollection(1).Points(1).Interior.Color = myCell.Offset(, i).DisplayFormat.Interior.Color
End Select

As far as T2 being at the bottom left: rotate the donut. Right-click one of the segments, choose Format Data Series…, The topmost thing under Series Options is the angle of the first slice. Slide it where you want it (manually enter values in one of the rows on the sheet to make it obvious what's being plotted where).

Foxmann250
07-27-2015, 01:56 PM
My plan is to copy this code over for 8 different sheets since I will have 8 different heat maps that will need to be compared eventually. I made the code changes and rotated the chart to fit and it looks great!

If I wanted to take the charts from each sheet and make another sheet that was kind of an overview sheet of all 8 heat maps for a side by side comparison, would I have to have the code for that new sheet reference sheet 1 for chart 1, sheet 2 for chart 2, etc?

p45cal
07-28-2015, 01:55 AM
Will the 8-chart sheet be dynamic? If so what will change it? Individual mini-charts with its own spin button, or change according to the single-chart-on-a-sheet versions, or perhaps all change in parallel with a single?

Foxmann250
07-28-2015, 02:05 PM
Yeah, I was going to have it be dynamic and make a Spin Button linked to a cell containing the "Minutes" so that as you cycle through the minutes ALL the graphs change. I was actually trying to create a spin button on the other sheets as well for cell L1 but it looks like using the Spin Button doesn't trigger the code to run so it changes cell L1's value but the chart stays the same.

p45cal
07-28-2015, 03:36 PM
You can work around that:
Use an ActiveX spinbutton instead, don't set the linked cell property, but set its code (right-click the spibutton and choose View Code)to be like:
Private Sub SpinButton1_Change()
Range("L1") = SpinButton1.Value
End Sub
This will trigger the Worksheet_Change event.
Where you have code like:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$1" Then
If IsNumeric(Target.Value) Then
If SpinButton1.Value <> Target.Value Then
SpinButton1.Value = Application.Median(Target.Value, SpinButton1.Max, SpinButton1.Min)
Target.Value = SpinButton1.Value
End If
Else 'someone's entered non numeric value so replace with spinbutton value:
Target.Value = SpinButton1.Value
End If
End If
End SubThis is because Spinbuttons have a max and min value that are set in its properties (in Design Mode, select the spinbutton and press F4 on the keyboard or right-click and select Properties). A spinbutton cant have a value outside these values so if someone manually enters a number which is out of range (or in range for that matter) the spinbutton's value gets adjusted accordingly: if the value is within limits then the spin button takes on that value, if the user puts in a value greater than the max then the spin button's value gets set to its max, and if the user enters a number less than the min the spinbutton's value becomes its min.
It also handles people putting non-numbers in L1.

You can also make the spinbutton values cycle, so that once the max has been reached, a further attempt to increase its value bring it to its min value and vice versa:
Private Sub SpinButton1_SpinDown()
Static LastValue
If SpinButton1.Value = SpinButton1.Min And LastValue = SpinButton1.Min Then
SpinButton1.Value = SpinButton1.Max
End If
LastValue = SpinButton1.Value
End Sub

Private Sub SpinButton1_SpinUp()
Static LastValue
If SpinButton1.Value = SpinButton1.Max And LastValue = SpinButton1.Max Then
SpinButton1.Value = SpinButton1.Min
End If
LastValue = SpinButton1.Value
End Sub


Or you can do similar with a scrollbar instead.

Foxmann250
07-28-2015, 05:13 PM
Ahhh, okay, unlinking cell L1 in the properties and inputing the first code there worked great! It would be cool to have the spinbutton value cycle back, I may implement that later once I have the basics all complete. :)


I'm currently trying to make that summary sheet that has all 8 graphs on it. I've been playing around with the codes on the other sheets to try and get this to work.


If my new charts are labeled Pie 1A through Pie 8A, and Donut 1A through Donut 8A and I want them to change color as cell L1 on sheet 9 changes can I reference the other sheets for data like so and repeat per sheet per chart?

Module 1:

Sub Sheet9ChartColor()
Set myCell = Worksheets("Sheet1").Range("B2").Offset(Worksheets("Sheet9").Range("L1").Value)
For i = 1 To 7
Select Case i
Case 2 To 7
ActiveSheet.ChartObjects("Donut 1A").Chart.SeriesCollection(1).Points(i - 1).Interior.Color = myCell.Offset(, i).DisplayFormat.Interior.Color
Case 1
ActiveSheet.ChartObjects("Pie 1A").Chart.SeriesCollection(1).Points(1).Interior.Color = myCell.Offset(, i).DisplayFormat.Interior.Color
End Select
Next i
End Sub


And then on the sheet call the subs?

Sheet 9:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheet9ChartColor
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Worsheets("Sheet1").Range("A3:I63"), Target) Is Nothing Then Worksheets("Sheet9").Range("L1").Value = Target.Row - 2
End Sub

p45cal
07-29-2015, 05:46 AM
There are many ways to go about this. Perhaps using the Camera tool would be the easiest; no need to duplicate charts, the only thing you might need is to synchronise existing charts or provide a spinbutton/scrollbar for each camera shot which adjusts L1 on the originial sheet. Send/attach existing file.

Look at sheet1 of the attached; a camera shot of the heatmap chart, now adjust the scrollbar.
Not difficult to add lines to the srollbar change event:

Private Sub ScrollBar1_Change()
Sheets("W1_15-07-20_CON_09-04_49_ShapeI").Range("L1") = ScrollBar1.Value
'Sheets("another1").Range("L1") = ScrollBar1.Value
'Sheets("another2").Range("L1") = ScrollBar1.Value
'Sheets("another3").Range("L1") = ScrollBar1.Value
'Sheets("another4").Range("L1") = ScrollBar1.Value
'Sheets("another5").Range("L1") = ScrollBar1.Value
'Sheets("another6").Range("L1") = ScrollBar1.Value
'Sheets("another7").Range("L1") = ScrollBar1.Value
End Sub

Foxmann250
07-29-2015, 09:15 AM
Great idea on the camera shot. I didn't know excel had that feature but that should save me a bunch of effort here. Here's what I have so far. I have all the camera pics on sheet 9 and am just running into a bit of code error when trying to have my Sheet 9 Spin Button adjust all the other cell L1's on sheets 1 through 8.

14029

p45cal
07-29-2015, 12:18 PM
Great idea on the camera shot.I had a play with the camera shot idea, got it working, unfortunately I decided to abandon it because it was taking too long to update the 8 charts and their camera shots, so back to plan A of 8 proper charts on the CompareAll sheet. These 8 charts are independent of the charts on the individual sheets. Update is instant. The only code that runs is the code in that sheet's code-module.

See attached.

Incidentally, whenever I see repetitive code I like to try and condense it - it saves having to change 8 bits of code in exactly the same way during development. I moved the worksheet_change event from the sheets to the ThisWorkbook module Workbook_SheetChange event.

Foxmann250
07-29-2015, 11:51 PM
Yeah I definitely noticed the lag of loading those pics every time I used the spin button. I think you're right in going this route.

I took the code you had on your file and tried to apply it to mine since I have been cleaning it up and adding new data. I switched the scrollbar to a spinbutton on the last page and organized my charts a bit. What I found is that the Donut charts are working fine on my sheet but for some reason the pie charts aren't changing color when using the spin button. I double checked the code from your file and mine and everything looks the same except for the scrollbar to spinbutton change and the addition of having the spinbutton change the value of cell N1 on the last sheet. Maybe you can take a quick peek and let me know what I'm missing and why my pie charts dont work but yours do.

14034

p45cal
07-30-2015, 04:33 AM
Apart from no data in sheets FP 7 and FP 8, there are errors in columns C of FP 3 and FP 4, which column is responsible for the pie chart colour.
But that's trivial compared to all the pie charts in Compare All having 4 series. They should have only 1 each.
These pie charts (all the pie charts in the workbook actually) don't need to refer to any cells at all (they don't even have to be charts; they could be circles or squares (any shape which filled the hole in the Donut chart) since the macros determine the colour). Currently the 4 series all refer to a cell for the Title of the chart (not needed) and a cell for the value (100%) but this can be hard coded as just 1, and 3 of them removed, which I have done with all the charts on Compare All.
I've also removed Module 1 since it's no longer used.

So only the 4 leftmost charts work due to data missing in their respective sheets.

See attached.

Foxmann250
07-31-2015, 12:37 PM
Thanks for the fix! The no data in FP 7 & FP 8 was intentional since this is real data and we did not use those 2 fireplaces. The "errors" on the page in FP 3 & FP 4 I wrote in. I had a thermocouple malfunction in the center of both of those fireplaces so that was also intentional. I thought I had looked at all the sheets on your file and mine and saw that they were the same. I forgot to remove the code on module 1 though.

I'm still not quite sure where I went wrong haha but thank you! You have been a huge help and this sheet is exactly what I was looking to do.