+ Reply to Thread
Results 1 to 10 of 10

Conditional formatting in one formula

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,820

    Conditional formatting in one formula

    Hello everybody
    I need a conditional formatting rule (Just one rule)
    It depends on cell B2 value .. If cell B2 value is Red then range("B5:E10") would be highlighted red
    If cell B2 value is Bule then range("B5:E10") would be highlighted Blue ...
    and so one

    I have ten values and need to apply ten colours .. I can manage conditional formatting for each color
    But I'm searching for a way to do that in one rule or using UDF function that achieve that task

    I imagine something like that
    Please Login or Register  to view this content.
    and use this UDF function in Conditional formatting
    But I have simple idea about colorindex colors and don't know if it is possible to use UDF function in conditional formatting rules or not

    I used this rule
    Please Login or Register  to view this content.
    It is ok .. I have to double click in cell B2 to have my results.
    I need to select a color from the list in B2 and got the desired color in my range at once
    Attached Files Attached Files
    Last edited by YasserKhalil; 07-24-2015 at 03:26 PM.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Conditional formatting in one formula

    Hi Yasser,

    This bit of code as an EVENT macro on the appropriate sheet (not a module) will do the trick.

    You may have to modify the Interior Index number - I think the color pallet has changed. I am using XL2013, ng get some strange colours. I used the numbers you indicated in your attachment.

    Please Login or Register  to view this content.
    I hope this helps, please let me know!

    Regards,

    David


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

    If several people have responded, when you reply please make it clear WHO you are responding
    to by mentioning their name.

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,820

    Re: Conditional formatting in one formula

    Thanks Mr. David for this great idea
    I really think about it and it is very good idea

    But I need UDF function to do that task ...
    It works for me till now but I have to double click in B2 to be executed ...
    Thanks for offering help

  4. #4
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Conditional formatting in one formula

    You're welcome Yasser,

    I tried your method, but couldn't get it to work the way you described. It was B5 which did not change, and I had to double click. Is that what you meant?

    Sorry, but I don't know how to resolve this, however, you should add one extra line to your function.

    Case Else: rngToColor.Interior.ColorIndex = 0 - just to reset the colours when there is an error.

    HOWEVER, I discovered one little trick! After I entered the =ColorRange($B$2,$B$5:$E$10) into the Conditional Formatting, It automatically set up =$B$5:$E$10 in the Applies to box.

    I changed this to =$A$5,$B$5:$E$10 and it worked!

    Yasser - Test.xlsm

    I hope this helps, please let me know!

    Regards,

    David


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

    If several people have responded, when you reply please make it clear WHO you are responding
    to by mentioning their name.

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,820

    Re: Conditional formatting in one formula

    Thanks Mr David for great help
    I tested it and it worked well but while I was working on it there is an error message "Excel has stopped working" .. I tried again and the same error appears while working on the file

  6. #6
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Conditional formatting in one formula

    Didn't get that for me, but there was some erratic behavior.

    I think this is being caused by the UDF not working well with Conditional Formatting.

    The solution I gave you originally does not use CF, but just shades the cells. Frankly, I prefer this!

    Someone more skilled than me may have to contribute!

    I hope this helps, please let me know!

    Regards,

    David


    - Please click on the *Add Reputation button at the bottom of helpful responses.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

    If several people have responded, when you reply please make it clear WHO you are responding
    to by mentioning their name.

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,820

    Re: Conditional formatting in one formula

    Ok I will use this method
    Please Login or Register  to view this content.
    I just need how to know the color indexes so as to add more colors and edit the code properly

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,820

    Re: Conditional formatting in one formula

    I just need how to know the color indexes so as to add more colors and edit the code properly
    Please help me

  9. #9
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Conditional formatting in one formula

    Just go to this site :https://msdn.microsoft.com/en-us/lib...ffice.12).aspx

    Regards,

    David

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,820

    Re: Conditional formatting in one formula

    Thanks for this useful link
    Thank you very much Mr. David

+ 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: 3
    Last Post: 06-19-2015, 07:16 AM
  2. Formatting Cells with Date or Text Values in a Conditional Formatting Formula
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 09:36 AM
  3. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  4. If Formula - conditional formatting - three different formatting rules
    By sharper1989 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-25-2014, 04:49 PM
  5. Replies: 3
    Last Post: 11-28-2013, 02:11 AM
  6. Replies: 1
    Last Post: 07-19-2012, 05:37 AM
  7. Replies: 6
    Last Post: 03-12-2006, 06:30 PM

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