+ Reply to Thread
Results 1 to 9 of 9

Using VBA for conditional formatting

  1. #1
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    132

    Using VBA for conditional formatting

    I am attempting to make a vehicle fleet maintenance workbook which contains 2 worksheets, on sheet2 I have many rows of vehicles details, 3 cells in each row have conditional formatting applied, enabling each of them to independently change to any of 3 colours (triggered by a vehicles proximity to different dates using the TODAY() function). On sheet1 I want to have a single corresponding column which will detect if any or all of the 3 cells in sheet2 have been "triggered" and display this by changing the cell colour. Phew...sorry if thats a bit long winded. I have little or no experience of VBA but hope there may be a formula I can utilise to help.

    Many thanks in anticipation.

  2. #2
    Forum Contributor
    Join Date
    01-13-2006
    Location
    Texas
    Posts
    161
    This may not be as sophisticated as you may have envisioned, but I find simplicity appealing.

    Why don't you use three extra cells per row on sheet 2 to indicate vehicle date proximity with 0/1, or 1,2,3, etc in addition to the color indicator. Then on sheet 1 set the color by using simple math comparisons instead of checking colors?

  3. #3
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    132
    Many thanks for taking the trouble to reply. I tried to keep my description of my worksheet as brief as possible but there are other parameters each row takes into account. For instance each vehicle row also refers to a code letter which changes the conditional formatting date ranges depending on the vehicles importance. You are probably right and a simple solution might be staring me in the face, but for the life of me I cant see it. I hope a VBA code is possible that picks up if any of a range of 3 cells in sheet2 has effectively returned a "true" conditional formatting outcome and indicating this on a single cell in sheet1 by changing a cells colour. Thanks again and regards.

  4. #4
    Forum Contributor
    Join Date
    01-13-2006
    Location
    Texas
    Posts
    161
    You may really want a macro or you may simply want to see how colors are treated in VBA code for whatever reason and that's OK. I don't mean to be argumentative.

    But any info that is used to conditionally format some cells with color, can also be used to set numeric values, for instance. So if the conditions set one cell to "Red", they can also set another cell to "1". I hope I do not belabor the point, but making the sheet 1 cells conditional will be a lot easier if the test does not involve color.

    Good luck.

  5. #5
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    132
    Hi Coner.......Please excuse my ignorance, but I understand what you meant now and will give it a try. Really appreciate your advice...Thanks and Regards.

  6. #6
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    132
    Sorry to be a pain...but I cant figure out how to return a numeric value to a cell based on conditional formatting. At the moment my lengthy formula just changes the cell colour, can you give me a simple example of how to give a cell a numeric value automatically through conditional formatting? I think I should be able to figure it out from there (I hope!) Thanks and Regards.

  7. #7
    Forum Expert
    Join Date
    01-12-2007
    Location
    New Jersey
    Posts
    2,127
    Nigel:

    It's a bit hard to envision your spreadsheet. Can you post a .zip sample of the workbook detailing what you would like to do?

  8. #8
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    132
    Hi...Will try (never done that before!)....I will have it done by tomorrow if you can keep an eye open for it. Many thanks for your interest. Regards Nigel

  9. #9
    Forum Contributor
    Join Date
    07-28-2005
    Posts
    132
    Hope that worked...I have uploaded (I think!) a section of my first attempt at making a Vehicle maintenance spreadsheet and changed the colour scheme to help explain its function. The yellow section at the top is normally hidden and contains the adjustable parameters depending on the vehicles importance. In the spreadsheet I am trying to construct the blue section will be the only visable part on sheet 1. The green section will be on a seperate worksheet (1 worksheet per vehicle and will be accessed via a hyperlink from sheet 1) and will show in more detail the vehicle history. Currently the "tyre manufacture date" Vehicle last check date" and "Battery Manufacture date" change colour to warn when action is required, Im looking to keep that facility on sheet 2 but somehow link it to sheet 1. When any of those three cells on sheet 2 are triggered I am looking to have ONE cell in the appropriate row change colour on sheet 1 (in a new column yet to be added). Many apologies for the length of the reply and I hope you can envisage what I am trying to do with VERY limited experience! Regards Nigel.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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