Search code examples
excelvbaconditional-formatting

Need VBA code and syntax to automate Conditional Formatting across multiple columns


My goal is to find the UNIQUE MINIMUM value in each COLUMN having a Range D2:D13, E2:E13..... to V2:V13, with the EXCEPTION of Colum M. (This is 19 columns, skipping the 10th column). Each cell in the Range D2:V13 is light blue formatted, and I want to highlight the UNIQUE MINIMUM for each COLUMN....if there is a UNIQUE MINIMUM. Each cell with a UNIQUE MINIMUM will be WHITE an all others in that column will remain the light blue. All help would be much appreciated.

Sample of spreadsheet (Note, I've only added conditional formatting to Columns D, E, and F)

Sample of spreadsheet

I've successfully used this code for a single column, but would like to automate it across all 13 columns. I have 6 different tables, with various number of rows from 50 to 100 rows in each table. I believe the Range...Select and the FormatConditions.Add statements have to have the column reference changed for each column and that everything after is common. Below would be the code for Column D only.

Range("D2:D13").Select   
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _   
    "=AND($D2=MIN($D$2:$D$13),COUNTIF($D$2:$D$13),$D2)=1)"   
' Below is the same for all columns   
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority   
With Selection.FormatConditions(1).Font   
    .Bold=True   
    .Italic=False   
    .TintAndShade=0   
End With   
With Selection.FormatConditions(1).Interior   
    .PatternColarIndex=xlAutomatic   
    .Color=vbWhite   
    .TintAndShade=0   
End With   
Selection.FormatConditions(1).StopIfTrue=False   

Solution

  • --blue cells

    semicolon separator

    =IF(AND(COUNTIF(A$2:A$6;MIN(A$2:A$6))=1;A2=MIN(A$2:A$6));FALSE;TRUE)
    

    coma separator

    =IF(AND(COUNTIF(A$2:A$6,MIN(A$2:A$6))=1,A2=MIN(A$2:A$6)),FALSE,TRUE)
    

    --white cells

    semicolon separator

    =IF(AND(COUNTIF(A$2:A$6;MIN(A$2:A$6))=1;A2=MIN(A$2:A$6));TRUE;FALSE)
    

    coma separator

    =IF(AND(COUNTIF(A$2:A$6,MIN(A$2:A$6))=1,A2=MIN(A$2:A$6)),TRUE,FALSE)
    

    enter image description here enter image description here