Using Excel VBA to apply conditional formatting on another workbook based on different rules

1
votes

I have a large database and I am running a vba script in order to create many different workbooks which contain only some sample data from the full database. This is working well, I am using a matrix in order to get all the relevant entries and then pasting the whole matrix into a defined range of a new workbook (and not copying cell by cell from one worksheet into the other one). My problem is now that I need to add two rules for conditional formatting.

I am looking for something such as:

 Application.Worksheets("Database").Cells(k, ColumnOfInterest).Select

  With Selection
    .FormatConditions.Delete
    .FormulaR1C1 = "=RC[-3] =""A"""
    .FormatConditions.Add Type:=xlExpression, Formula1:=.FormulaR1C1Local
    .FormatConditions(1).Interior.ColorIndex = 6
    .Formula = ""

End With

  With Selection
    .FormatConditions.Delete
    .FormulaR1C1 = "=RC[0] ="""""
    .FormatConditions.Add Type:=xlExpression, Formula1:=.FormulaR1C1Local
    .FormatConditions(1).Interior.ColorIndex = 5
    .Formula = ""

End With

In other words, when the user has selected "A" in the 3rd cell to the left, then the color of the cell of relevance should be color index 6, if the cell of interest is empty, then the color code is 5. Unfortunately this code does not work and only creates one single rule for conditional formatting.

1
excelvbaformattingconditional

1 Answers

2
votes

.FormatConditions.Delete deletes the previous formatcondition. If you remove .FormatConditions.Delete in the second part and change .FormatConditions(1) to .FormatConditions(2) it should work:

Application.Worksheets("Database").Cells(k, ColumnOfInterest).Select

With Selection

    .FormatConditions.Delete

    .FormulaR1C1 = "=RC[-3] =""A"""
    .FormatConditions.Add Type:=xlExpression, Formula1:=.FormulaR1C1Local
    .FormatConditions(1).Interior.ColorIndex = 6
    .Formula = ""

    .FormulaR1C1 = "=RC[0] ="""""
    .FormatConditions.Add Type:=xlExpression, Formula1:=.FormulaR1C1Local
    .FormatConditions(2).Interior.ColorIndex = 5
    .Formula = ""

End With