In my case I am applying formatting on a line by line basis (in view you would see your Closing Stock number for that line item and I want the formatting to be based on Stock on Hand days, for example, which is calculated somewhere else) spread across different zones (columns).ģ.
OUTLOOK CONDITIONAL FORMATTING SUM OF CELLS CODE
If the above is not possible then how would I edit the code so that it applies to multiple source and target ranges. So in your example you would use excel’s built in colour scales to create the original conditional formatting (Source Range) and then use the VBA sub to clone that formatting onto the Target Range.Ģ. Is it not simply possible to use a macro to copy formats from one range to also apply to another. Since my VBA coding/editing skills aren’t something to be proud of, your help would be greatly appreciated.ġ. I have a similar albeit slightly more complex problem that I think the VBA version of the solution would be more suited to. Here’s a workbook demonstrating these two methods.
OUTLOOK CONDITIONAL FORMATTING SUM OF CELLS UPDATE
I’m not sure how practical this is, but it was fun to figure out! Obviously, you’d want to tie this to a worksheet or pivot table event to update the formatting when the values change. Cells(i, j).Interior.Color = CellColor(i, j) IIf(ScalePosition < 127, ScalePosition * 2, Abs(ScalePosition - 255) * 2)) 'this formula goes from blue to red, hitting white - RGB(255,255,255) at the midpointĬellColor(i, j) = RGB((ScalePosition * 2, 255), _ ScalePosition = (var(i, j) - MinValue) * (1 / ScaleIncrement) 'the scale position must be a value between 0 and 255 ReDim CellColor( UBound(var, 1), UBound(var, 2)) 'if all source cells have the same value or there's only one ScaleIncrement = (MaxValue - MinValue) / NUMBER_OF_INCREMENTS 'divide the range between Min and Max values into 255 increments MsgBox "Source and Target ranges must be" & vbCrLf & "same shape and size" Sub ConditionalFauxmatting(rngSource As Excel.Range, rngTarget As Excel.Range) The colors are then assigned to the target range: This is done by dividing the range of values source values into 255 increments. It assigns each of those values a color in a scale from green to red, with white in the middle. It finds the highest and lowest values in the source range. The following subroutine takes two ranges – a source and a target range – as its arguments. You may have to force recalculation with F9 to have the picture update.įor one project I augmented this method by writing code that let me toggle back and forth between the values in the pivot table and the values the conditional formatting is based on.Īs the heading implies, this method attempts to replicate conditional formatting using VBA. One thing to be aware of is that the picture doesn’t update until there’s a worksheet recalculation. The conditional formatting is based on these cells, whose text is invisible because of the custom format. The cells underneath the picture contain the formulas referring to the pivot table. The source pivot table is in rows 11 to 18, and you can see that the picture starting in row 2 is linked to it. The result will be like the illustration below. This way you can see through the picture to the conditionally formatted cells underneath.
![outlook conditional formatting sum of cells outlook conditional formatting sum of cells](https://support.monday.com/hc/article_attachments/360024627980/Screen_Shot_2021-04-06_at_11.09_1.png)
Set the picture to be completely transparent, using the “no fill” setting. Use the camera tool to take a picture of the entire pivot table and paste it over the range you just created, lining up the conditionally formatted cells.All you want to see is the conditional formatting. Format the numbers in that range to be invisible, by using a custom format of “ ”.Create the range of formulas that you’ll base the conditional formatting on.In this case the formatting is applied to a pivot table, but you can do it with any range. This method uses Excel’s under-publicized camera tool, which creates a live picture linked to a group of cells.
![outlook conditional formatting sum of cells outlook conditional formatting sum of cells](https://thesoftwarepro.com/wp-content/uploads/2019/02/outlook-conditional-formatting-03.png)
So, here’s my two approaches to doing this: The colors range from red at the low end to green at the high end: Instead, it reflects the values in the second table, each cell of which contains the difference from the previous year in the pivot table. The color formatting isn’t based on the values in the pivot table. The first uses the camera tool while the second is a VBA subroutine that mimics conditional formatting.īelow is an example of what I’m talking about. In this post I discuss two ways to base color scales on another range. However, it’s more complicated if you want color scales derived from values in another range. With formula-based conditional formatting, it’s pretty easy to base the formats on other cells in the workbook, simply by referring to those cells in the formula.