• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change color of bar chart depending on cell value

W

Wim Geuens

Member
Hello,

me again :)

is it possible to change the color of a data bar (columns) to the color that is generated by the conditional formatting.
I am able to put a color on the databar, but it doesn't take the color from the conditional formatting.
the link to that is herein:
http://datapigtechnologies.com/blog...rt-series-colors-to-match-source-cell-colors/

It should be perfect if it takes the color of the conditional formatting, because right now it doesn't

can someone help?

Thanks
 
Hi Wim ,

It may or may not be possible , since a lot of people have requested it but the author has not responded.

However , the following line in the code retrieves the color of the worksheet cell :

SourceRangeColor = SourceRange.Interior.Color

When a cell is conditionally formatted , the following statement can retrieve the color of the cell :

Activecell.FormatConditions(1).Interior.color

This assumes that the cell has only one CF formula.

If you can upload a complete workbook with the data and the chart , it will make everyone's job easier.

Narayan
 
Hi, Wim Geuens!

Sorry for not having the time to elaborate right now (and maybe until a couple of days I won't) but maybe this helps you. You can check CF for a cell testing this property:
.FormatConditions.Count

Then for each format condition you'll have to get messed with this 4 properties:
.Type
.Operator
.Formula1
.Formula2

Where type is a member of this data type: xlFormatConditionType
NombreValorDescripción
xlAboveAverageCondition 12 Condición por encima de la media
xlBlanksCondition 10 Condición de celdas que están en blanco
xlCellValue 1 Valor de celda
xlColorScale 3 Escala de colores
xlDatabar 4 Barra de datos
xlErrorsCondition 16 Condición de errores
xlExpression 2 Expresión
XlIconSet 6 Conjunto de iconos
xlNoBlanksCondition 13 Condición de celdas que no están en blanco
xlNoErrorsCondition 17 Condición sin errores
xlTextString 9 Cadena de texto
xlTimePeriod 11 Período de tiempo
xlTop10 5 10 valores más frecuentes
xlUniqueValues 8 Valores únicos
This is in my Spanish version, but you can retrieve the one in your local language searching within the VBA help for FormatCondition.Modify

For Operator, member of xlFormatConditionOperator:
NombreValorDescripción
xlBetween 1 Entre. Sólo se puede usar si se proporcionan dos fórmulas.
xlEqual 3 Igual que.
xlGreater 5 Mayor que.
xlGreaterEqual 7 Mayor o igual que.
xlLess 6 Menor que.
xlLessEqual 8 Menor o igual que.
xlNotBetween 2 No entre. Sólo se puede usar si se proporcionan dos fórmulas.
xlNotEqual 4 No igual que.
Optional if not formula used, i.e., Type <> xlExpression.

Formula1 and Formula2 are the actual either conditions (Formula2 only for xlBetween and xlNotBetween) or formula definition (Formula1 for xlExpression).

And then you have to deal with the objects .Font and .Interior (and each of their properties) to get the customized definition each condition.

Are you ready to go thru all this?

Hope it helps.

Regards!
 
@NARAYANK991
Hi!
I think I'd guess why the author didn't answered, and so should I'll hide myself too. Only once I had to get my hands dirty with this kinda stuff and there were just very simple conditions with value ranges.
Regards!
 
My boss told me yesterday when i asked him this question as well :)

No I don't need it straight away, but how much time do you think you need?
I can do the other changed that needs to be done to the excel file meanwhile

thank you so much for putting you effort into this. it really helps me alot
 
Hi Wim ,

At most day after tomorrow , but I'll know by tonight whether I can do it or not. Let me try.

The only point is that the macro will be tailored for your file as it stands ; if there is any change , the macro will also have to be changed.

Narayan
 
Hello all,

One other possible idea, instead of using coding, is to use several dummy series. Assuming that the color of the cell in question is determined by some condition, and not willy-nilly the boss is just picking random colors, you could use formulas that use those same conditions. So, imagine we create 3 dummy series of red, yellow, green. Formulas are:
=IF(SourceCell>100,SourceCell,NA())
=IF(AND(SourceCell<=100,SourceCell>90,SourceCell,NA())
=IF(SourceCell<=90,SourceCell,NA())

You then plot the 3 series on your chart and color appriately. The formulas make sure that only 1 series is ever displayed at any one time, and it "looks" like the chart is changing color just like the cell.

Anyway, that's my 2 cents.
 
Hi Luke ,

Thanks for the suggestion ; let me go through the uploaded workbooks and I'll post back my comments , and the solution if I can make it work.

Narayan
 
Hi,
I'm trying to change the colour of a bar on an excel chart if the value in a specific cell is within a certain range. For example, if the categories for each series in the chart are 2, 4, 6, 8, 10 and a value is entered into cell A9, I want the bar closest to that number to change colour from blue to green.
 
jahangeer100 said:
Hi,
I'm trying to change the colour of a bar on an excel chart if the value in a specific cell is within a certain range. For example, if the categories for each series in the chart are 2, 4, 6, 8, 10 and a value is entered into cell A9, I want the bar closest to that number to change colour from blue to green.
Click to expand...
Hi Jahangeer,
You should start a new thread for your own question, rather than "hijacking" an existing one, as most people will assume this thread has already been answered.
Thanks.
 
You must log in or register to reply here.
Back
Top