+ Reply to Thread
Results 1 to 7 of 7

Using VBA to apply Conditional Formatting based on a calculation to Column

  1. #1
    Registered User
    Join Date
    11-08-2014
    Location
    london
    MS-Off Ver
    2013
    Posts
    49

    Using VBA to apply Conditional Formatting based on a calculation to Column

    Hi I have been looking through posts to find the answer but I am getting more confused. Hopefully some kind person can show me the code...

    I have 3 columns of numeric data in my spreadsheet that has been populated by a 3rd party import application (I cant access the spreadsheet until after it is populated from this application which removes all conditional formatting but does keep any VBA code etc). The 3 columns are fixed position but there are hundreds of rows (possibly a different number of rows each time the import is run)

    I need to see if Column 1 + Column 2 <> Column 3. If so then I need column 3 to be highlighted (lets say in red background).

    I would be very grateful if someone could provide me with the code to apply the formatting. I would launch it from a command button.

    Thank you so much.

    Tony

  2. #2
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Using VBA to apply Conditional Formatting based on a calculation to Column

    Hi Tony..
    Try this code & a workbook is attached for you to play with.
    I think it does what you're after.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-08-2014
    Location
    london
    MS-Off Ver
    2013
    Posts
    49

    Re: Using VBA to apply Conditional Formatting based on a calculation to Column

    Hi Phil. Thank you for doing this. Works a treat but unfortunately it is not quite what I need. I did not explain clearly - Sorry to have got you to do the work.

    These 3 columns are going to be input fields and their values can change. The conditional formatting is to let the user know if the values should be looked at. Hence if on start up col 1 + col 2 = col 3 then the formatting would be blank. If someone enters a value in any of the 3 columns that makes col 1 + col 2 <> Col 3 then the background in col 3 goes red until they correct the values.

    What I need is the code to set the conditional formatting on column 3. I believe it is a function called "FormatConditions"

    Hope this makes it a bit clearer.

    Many thanks again.

    Tony
    Last edited by tonygg9; 11-11-2014 at 06:56 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Using VBA to apply Conditional Formatting based on a calculation to Column

    Try this. I've changed it to a worksheet change event for any of the cells in the range A1:C? (bottom row on column C) & on workbook open it triggers the same macro. If you don't need the workbook open option, comment out or delete the code in the Workbook module. Hopefully that covers all bases
    Cheers
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-08-2014
    Location
    london
    MS-Off Ver
    2013
    Posts
    49

    Re: Using VBA to apply Conditional Formatting based on a calculation to Column

    Many thanks. I am out tonight so will use tomorrow. Many thanks for all your help. Tony

  6. #6
    Registered User
    Join Date
    11-08-2014
    Location
    london
    MS-Off Ver
    2013
    Posts
    49

    Re: Using VBA to apply Conditional Formatting based on a calculation to Column

    All sorted. Many thanks Tony

  7. #7
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Using VBA to apply Conditional Formatting based on a calculation to Column

    No Problem

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 05-28-2014, 06:31 PM
  2. [SOLVED] Add border & apply conditional formatting to sheets based on cell value
    By amar05 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2013, 11:02 AM
  3. VBA for Conditional Formatting - apply a colour based on conditions in each column.
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-27-2013, 01:24 PM
  4. How to apply conditional formatting to a whole column
    By scottghansen79 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2010, 05:21 AM
  5. HOW CAN I APPLY CONDITIONAL FORMATTING BASED TO AN ENTIRE ROW?
    By James M in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-19-2005, 11:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1