Custom Format Cell when a cell contains certain text

[#1]

Hi all,

I have a problem which could be solved with a function that doesn't exist, but am hoping somebody can work out a work-a-round to help me out with this:

I have a straight table with many dimensions but am trying to format the background colour of some of the cells in those columns.  I want to highlight cells that contain a specific symbol in the text ('>'), but cant find a way of doing it in a formula that i can just apply to all dimensions as i need to reference the name of the dimension that corresponds to the cells in that column in any formula I can find.  And, after a while of searching, there is no function that calls the name of the dimension to apply to a formula.

I'd love to be able to do a =if(SubStringCount(ThisDimension(), '>'), vGreen, vWhite) or something...

So other than going into the background colour section of the collapsed menu of each dimension in the chart properties, and writing 70-odd individual formulae asking it to highlight anything containing a '>', is there a way, maybe using the custom format cell option (that has an 'apply to all dimensions' tick box), that I can just highlight any cell containing a '>'?  Or some sort of formula work-a-round that bypasses the need to reference the dimension name?

Hope all that makes sense!

Thanks in advance,

Lewis

[marcus_sommer]

Unfortunately such functionalities aren't available and you will need to do it manually.

- Marcus

[adamdavi3s]

do vote this up though!

https://community.qlik.com/ideas/2830

[vinieme12]

have you tried

=if(FindOneOf(Fieldname,'>')>0,red(),green()) ,

You will need to do this for all expressions/dimensions

[adamdavi3s]

This still requires the user to do this for every dimension?

[vinieme12]

The below macro will allow you to add background expression for all dimensions

SUB coloralldimension()

set chart = ActiveDocument.GetSheetObject("CH01")    ' <<Use Your chart object ID here

set cp = chart.GetProperties

n = cp.Expressions.Count

for i =  0 to (n-1)

set fld = chart.getfield(i)

set bce = cp.Dimensions(i).AttributeExpressions.BkgColorExp

bce.Definition.v = "=if(FindOneOf(" & fld.Name &",'>')>0,red(),green())"

chart.SetProperties cp

NEXT

END SUB

[vinieme12]

lewis.vaughan does this work for you!