Search code examples
excelvbaexcel-formulaconditional-formatting

VBA conditional formatting option for highlighting unmatching criterias

I have created a spreadsheet with two tabs.

The first worksheet is named "Overview 2023" and contains the results of a rather complex formula in the range of cells D4 to AJ159. The formula can either return 0 or a numerical value

enter image description here

The second worksheet is named "BL GL," which displays a list of BLs in column A and their corresponding GLs in column C. The challenge here is that some BLs may have multiple associated GLs.

enter image description here

My goal is to apply conditional formatting via VBA or not to highlight the values within "Overview 2023" (Cells D4:AJ159) as follows:

In gray: if the cell equals zero.

In green: if the cells with values other than zero match a BL number and GL number combination found in the "BL GL" worksheet (located in columns A and C). I have tried the below but it doesn't work. =AND(D4<>0, COUNTIF('BL GL'!$D$1:$D$159, D4)>0)

In yellow: if none of the above conditions are met (meaning if the cells contain a value, but no corresponding BL GL combination is found).

To put it simply, if there is a value under D4, the cell needs to be highlighted in green if C4 and D3 have a match under the BL GL tab (match between column A and C) If the math doesn't exist, the cell should be highlighted in yellow.

Solution

  • If you want to have the ones that have a value in the BL column but doesn't have a matching GL value yellow: here are the formulas and img:

    enter image description here

    Yellow

    =AND(D4<>0;COUNTIFS('BL GL'!$A$2:$A$10;RIGHT($C4;LEN($C4)-2);'BL GL'!$C$2:$C$10;D$3)=0;COUNTIF('BL GL'!$A$2:$A$10;RIGHT($C4;LEN($C4)-2))>0)

    Green

    =AND(D4<>0;COUNTIFS('BL GL'!$A$2:$A$10;RIGHT($C4;LEN($C4)-2);'BL GL'!$C$2:$C$10;D$3)>0)

    Grey (think you had this one already ^^)

    =D4=0

    If you want all of the ones that don't have a BL/GL match, this is the yellow one then:

    =AND(D4<>0;COUNTIFS('BL GL'!$A$2:$A$10;RIGHT($C4;LEN($C4)-2);'BL GL'!$C$2:$C$10;D$3)=0)

    Just a small trim on the formula

    For the formulas, you can edit the ranges for BL GL to $A:$A or the respective range you have, whichever you prefer.