Highlight cells that = 'No' across whole workbook

[thrnma]

Hello,

I have created an Excel in Alteryx. The table itself is fine and works, but I am now trying to set it up so that every time the word 'No' appears in the table, I want to highlight it. I figured out how to do it per column/field, but I cannot set it up for every field as the size of the table will change. 

The image above shows the fields that are currently in my table. However, next month, the table may have more or less iterations of the 4 variables (e.g. next month, there may be a 4_Comments, 4_Completion, 4_Reason and 4_Value). 

I basically want the table tool to look at every cell in the workbook, as opposed to having to set it up to look at specific rows/columns.

I tried by looking at specific rows (as the rows will not change, just the number of columns), but I could only highlight full rows and could not specify to look at just 'No'

Please can someone help.

Thank you

[atcodedog05]

Hi @thrnma ,

I am dont think we will able to set Rule at a Table level.

You need to define Rule either at a row level or column level. I tried applying rule on Dynamic unknown column also but it doesnt work.

I know its not a bright idea. But you can run through different table tool for different number of columns.

Hope something helpful shows up : )

[thrnma]

@atcodedog05 

Thank you for the information.

How would I go about doing it at a row level?

I find that doing it through this setting (Edit Row Rule), I can only highlight a full row and not then highlight just the 'No' values. Is there a way to set it up on this level?

Thanks

[atcodedog05]

Hi @thrnma ,

Click any column like below. The options will become active.

Click on Create here you will be able to create column rule. Ie is a value is true only that cell be colored like below color only 1

A workflow is attached

Hope this helps : )

If this post helps you please mark it as solution. And give a like if you dont mind😀👍

[danilang · Accepted solution]

Hi @thrnma 

Here's a dynamic, macro based solution that applies a column rule to all the columns in the incoming data.  It leverages the fact that the configuration of every tool in a workflow is represented by an snippet of XML and that this XML can be modified on the fly by the Action tool.  The tricky part is building the proper XML.  You can get a start by opening a workflow in Notepad and examining the XML

The macro itself is quite simple.  It simply has a basic table tool that is modified by a control parameter.  The Action tool connecting the 2 is configured like this

 

It's Action is Update Raw XML with Formula and the formula is [#1] which shorthand for use what ever string is coming in on connection #1.  It's also updating the outer xml, whihc includes the <StyleRules></StyleRules> tags

The main workflow is where you build the xml

Use a Field Info tool to get a list of the fields, a Formula tool to build the XML for each field rule and a Summarize to concatenate each rule and enclose it all in the Outer XML tags.  The formula I use looks like this

'<Rule Name="'+[name]+'Rule1">
   <RowRule value="False" />
   <HighPrecedence value="False"/>
   <Field>'+[name]+'</Field>
   <Test>Formula</Test>
   <ApplyTo>Data</ApplyTo>
   <SimpleVar />
   <SimpleOp>==</SimpleOp>
   <SimpleConst>1</SimpleConst>
   <Formula>[_CurrentField_] ="N"</Formula>
   <Style TextColor="#ff0000" />
   <PrettyName>'+[name]+' Rule 1</PrettyName>
</Rule>'

The part in italics [_CurrentField_] ="N" is where you apply the condition.  To check for "NO" as opposed to just "N", change it to  [_CurrentField_] ="NO" 

Since you use a field list to get the current list of fields, it adapts to any input data

The result look like this, with the "N"s in red

Hope this helps

Dan

[atcodedog05]

@danilang 

Sir you are just amazing😎 Just when we feel we hit a dead-end. You come up with an amazing solution.

This is a life saver!!

Is this macro available some where like gallery etc. I get a lot people asking me on how to set Table level rules.

And this one is perfect. It would be helpful for me to guide them to this macro.

[danilang]

Hi @atcodedog05 

You are too kind. 

The macro isn't available anywhere other than in this workflow.  It's meant as a learning tool and isn't production ready.  In its current state it's hard-coded for this specific problem.  A production ready version would include Interface tools to set the formats, conditional formulas, etc. as well as basic error checking.  

Dan

[atcodedog05]

@danilang 

Still its amazing i have seen a lot of posted question. Where i have hit a dead-end.

Where they want to color all the 1's or 0's or N's something similar this is perfect for those scenarios 🙂

[thrnma]

@danilang 

This is brilliant, thank you greatly for this!

@atcodedog05 

Thank you for your suggestions too, helped teach me a bit about the table tool which has been useful!

[BrianDumm]

Love this!  Been using it to learn how to do one for my own flow.  The only thing, for the life of me, I cannot seem to get it to change the cell color rather than the text color.

<Style TextColor="#ff0000" />   and I tried <Style background-color="#ff0000" />   and other variations:  Background, background Color etc...

Anyone have any thoughts?  IT would be greatly appreciated.