+ Reply to Thread
Results 1 to 3 of 3

Simplifying Conditional Formatting - Formula Assistance Please

  1. #1
    Registered User
    Join Date
    11-04-2012
    Location
    Perth, AU
    MS-Off Ver
    Excel 2007
    Posts
    13

    Question Simplifying Conditional Formatting - Formula Assistance Please

    Hello World!

    I have been working on an excel worksheet that is generated by one of our project reporting systems. The system exports to excel all of the financial information for the project being review. No two projects produce the same number of rows, however, there exists the same number of columns with the same headers.

    Under column C are detailed parent and child task numbers in no sequential pattern.
    Parent numbers are:
    10.01
    10.02
    20.03
    so on and so forth

    Child task numbers are:
    10.01.01
    10.02.01
    20.03.01
    so on and so forth

    I have created conditional formats that apply a red font color to any row in Column C that contains the Parent Task Numbers. While the conditional formats work there are over 20 Parent task numbers and consequently my conditional formatting code (in VBA) is 212 lines long! Here is a snipit:

    Please Login or Register  to view this content.
    The formula I intially entered into conditional formatting was "=$C5=10.01" repeated for each parent task level, but I have also tried a frankenstein AND/OR statement to include all parent task numbers "=$C5=AND(OR(10.01, 10.02, 20.03,......)" with no success.

    Can anyone else offer/see any solution to create a more simplistic conditional formatting formula for all parent task numbers?
    Format Painting/Copy and Pasting Special/Etc. not an option as these conditional format are to become part of a Macro

    Thank you all very much for any inspiration you can provide.

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2021
    Posts
    2,081

    Re: Simplifying Conditional Formatting - Formula Assistance Please

    As parent task numbers are only 5 characters long, can't you use this formula for your conditional formatting?
    Please Login or Register  to view this content.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Registered User
    Join Date
    11-04-2012
    Location
    Perth, AU
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Simplifying Conditional Formatting - Formula Assistance Please

    Thanks Pierre.

    I had to tweak the formula slightly as I am needing the entire row formatted font red.

    Please Login or Register  to view this content.
    The only exception to this conditional format that still requires the red font is the parent task 900.90. As you can see it is 6 characters. Tweaking the formula slightly I was able to get the correct formatting.

    Please Login or Register  to view this content.
    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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