Cell Count by Color

[jolene_82]

I have been working on an excel automation for refrigeration temperatures. I was attempting to color code cells in the same column based on temperature ranges. I was able to create a conditional formatting formula to color code each range. The last step I needed was to apply a formula to count the number of colored cells in each range. It appeared to work at first glance based on the table data, but once I filtered the data from largest to smallest, oldest to newest I realized the colored cell ranges were all shifted up by one cell. Meaning the first cell in the colored range did not meet the criteria but the last that did meet the criteria was left out. Any ideas? This is the code I used to first apply the conditional formatting formula and then the cell count formula. Just to answer the inevitable question, I did not use conditional formatting because the automation does not recognize the colored cells when using that process. Also, work computer prevents me from being able to download the XL2BB due to restrictions.

// Create custom from range C:C on selectedSheet
conditionalFormatting = sheet.getRange("C:C").addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
conditionalFormatting.getCustom().getRule().setFormula("=AND(ISNUMBER($C1), $C2>46.499,$C2<186)");
conditionalFormatting.getCustom().getFormat().getFill().setColor("#f4b084");
conditionalFormatting.setStopIfTrue(false);
conditionalFormatting.setPriority(0);
// Create custom from range C:C on selectedSheet
conditionalFormatting = sheet.getRange("C:C").addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
conditionalFormatting.getCustom().getRule().setFormula("=AND(ISNUMBER($C1), $C2>32.099,$C2<35.600)");
conditionalFormatting.getCustom().getFormat().getFill().setColor("#bfbfbf");
conditionalFormatting.setStopIfTrue(false);
conditionalFormatting.setPriority(0);
// Create custom from range C:C on selectedSheet
conditionalFormatting = sheet.getRange("C:C").addConditionalFormat(ExcelScript.ConditionalFormatType.custom);
conditionalFormatting.getCustom().getRule().setFormula("=AND(ISNUMBER($C1), $C2>0,$C2<32.100)");
conditionalFormatting.getCustom().getFormat().getFill().setColor("#9bc2e6");
conditionalFormatting.setStopIfTrue(false);
conditionalFormatting.setPriority(0);
// Set range H2:H4 on selectedSheet
sheet.getRange("H2:H4").setFormulasLocal([["=COUNTConditionColorCells(C2:C2000,F2)"], ["=COUNTConditionColorCells(C2:C2000,F3)"], ["=COUNTConditionColorCells(C2:C2000,F4)"]]);

Color Coding Based on Conditional Formatting Formula
Red - =AND(ISNUMBER($C1), $C2>46.499,$C2<186)
Gray - =AND(ISNUMBER($C1), $C2>32.099,$C2<35.600)
Blue - =AND(ISNUMBER($C1), $C2>0,$C2<32.100)

 

[Cubist]

Are you fixated on using VBA to do the count? You use the same formulas for conditional formatting to do the count.

 

[jolene_82]

I honestly have no experience with coding. I used the automation process to get most of process. I spent hours trying to find a way to color the cells and then count, but I was running into multiple issues. The standard conditional formatting process does not actually color the cell so many formulas I found to count the cells would not work with that process. Since I don't know what I am doing, I can say that I am not fixated on a program. I just need to be able to apply the process to the automation recording in Office Script. I was able to complete the steps using macro but the automation recording doesn't recognize the macro command. I am not sure how to explain my issue fully because I don't know the coding world. This code works perfectly except the shifting of the range up by one cell. I need to apply this code across every new workbook I open so that is why it is saved inside of an automation script with the other steps I set up.

 

[Fluff]

Try changing the C2 to C1 everywhere it exists in your code.

 

[jolene_82]

Are you fixated on using VBA to do the count? You use the same formulas for conditional formatting to do the count.

Try changing the C2 to C1 everywhere it exists in your code.

That was it!!!! I stared at that for hours and it never occurred to me to try that! My code is now perfect for what I need, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.