Search code examples
excelvbaconditional-statementsformats

Writing a Conditional Format Formula in Excel

I am trying to write a rule in vba to trigger a Conditional Format, but I have my doubts if I am writing it correctly.

The condition for the format would be, if the cell value of a range is equal to the cell value of the same range on a different sheet: =M7=Original!M7

This is to applied for all cells in a range (M7:BZ5000) [5000 is a large outer bound number to help conserve memory].

The issue that has me perplexed is how do I tell vba that M7=Original!M7, M8=Original!M8, Z143=Original!Z143, etc.

I feel this is a pretty basic concept but I searched these forums and could not find an answer that I think applies to my issue.

Also, Since I Would want this conditional format to trigger at the press of a button, how would I tell it to deactivate, WITHOUT removing the rest of my conditional formats (I have normal conditional formats applied to the range and want them to remain whenever the button isn't pressed.

  1. Sheets("Main").Select
  2. Range("M7:BZ5000").Select
  3. For Each Cell In Selection
  4. If Cell.Interior.Color = Excel.XlRgbColor.rgbBlack Then
  5. Cell.Clear
  6. End If
  7. Next
Solution

  • You are using the conditional formatting choice of "Use formula to determine which cells to format"

    The formula you will need is going to be like this (note the use of $ signs to control how the formula changes. The $ stops the value after it from changing)

    =AND($M7=Original!$M7,$G$4="Y")

    Note $G$4 is a cell you can use to turn on and off the cond. formatting. When it is "Y" the formatting will work, otehrwise any formatting you have applied will JUST be shown.

    If you don't want you formatting to show when conditional formatting is on, then your conditional formatting MUST have formats the remove yours and sets them to the "default" settings.