Can graph formatting be linked to source data formatting?

[engkinooi]

Is there a way to link the formatting of a graph to the formatting of the source data? For example, if I make a bar chart out of numerical data, can Excel link the color of each bar to match the font color of the source data? And if I change the font color of one particular data point, Excel automatically changes the color of the corresponding bar.

 

[6StringJazzer]

This can be done only with VBA. Even with VBA it is rather complicated because VBA cannot automatically a format change to cell, such as a font color change. You could add a button that does the updates on user demand. The actual code would be very specific to your chart and your data, so I can't offer a general solution.

 

[kevin9999]

As Jeff ( @6StringJazzer ) points out, Excel can’t detect a change in font colour, and only VBA will achieve the connection between series value font & chart series font you seem to be seeking. A lot depends on how & why your data values change.

I’ve offered some options below. The first (Demo 1) shows how you can have 2 series on a chart of different colours – and the bar value will change depending on which series the value sits in on the source data (determined by an IF function). Change (for example) January’s figure in cell F2 on the Demo file from 11 to 5 and see what happens with the bar colour for January.

Your requirement seems more complicated. Whereas you can’t trigger an event by a change in font colour – you can run some code if a value changes on a sheet. In the demo file, I’ve used a Worksheet_Change event to run the code that changes the bar colours in the second chart to whatever the font of the value in the source data is. To demonstrate, change the font colour in cell I2 (January’s value) to red – then either change the source value in cell F2 or, to use Jeff’s suggestion, run the code independently (in the demo file by pressing the button).

Data layout:

Chart Demos.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Month	<10	=>10		Month	Amount		Month	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	Jan		11		Jan	11		Jan	11
3	Feb		10		Feb	10		Feb		10
4	Mar	9			Mar	9		Mar			9
5	Apr	9			Apr	9		Apr				9
6	May		11		May	11		May					11
7	Jun		12		Jun	12		Jun						12
8	Jul		15		Jul	15		Jul							15
9	Aug	4			Aug	4		Aug								4
10	Sep	5			Sep	5		Sep									5
11	Oct	6			Oct	6		Oct										6
12	Nov		11		Nov	11		Nov											11
13	Dec	5			Dec	5		Dec												5
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=IF(F2<10,F2,"")
C2:C13	C2	=IF(F2>=10,F2,"")
I2	I2	=F2
J3	J3	=F3
K4	K4	=F4
L5	L5	=F5
M6	M6	=F6
N7	N7	=F7
O8	O8	=F8
P9	P9	=F9
Q10	Q10	=F10
R11	R11	=F11
S12	S12	=F12
T13	T13	=F13

VBA - put in a standard module:

Option Explicit
Sub Change_Color()
    Dim x As Long, s As String, c As Range
    ActiveSheet.ChartObjects("Chart 2").Select  '<~~ change to actual chart name
    For Each c In Range("I2:T13")               '<~~ demonstration range used
        If c <> "" Then                         '<~~ IF there is a value in the cell for this range, then...
            x = c.Characters.Font.Color         '<~~ gets the font colour
            s = Cells(c.Row, 8).Value           '<~~ gets the name of the series from column H
                ActiveChart.SeriesCollection(s).Interior.Color = x
        End If
    Next c
    [A1].Select
End Sub

VBA - put in the worksheet module of the sheet with the data & charts (right click the tab name, select View Code, paste code into the empty module that appears)

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

Link to demo file: Chart Demos.xlsm