+ Reply to Thread
Results 1 to 8 of 8

Color-based conditional equation?

  1. #1
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    104

    Color-based conditional equation?

    Hi Forum,
    There are values under "Expenditure_1" variable that are highlighted by yellow color (attached).
    In the original dataset too they are highlighted in yellow color.

    Question:
    Of the values highlighted by yellow colour under variable "Expenditure_1", I wanted to return
    only values that are found under "Expenditure_2" variable too.

    Answer is provided.
    Value 300 should not be returned under the answer because that value is not found under Expenditure_2 variable although it is
    highlighted by yellow color under Expenditure_1 variable.

    Could any expert please help me to automate the answers as I have several thousand observations in my dataset.
    I think a function has to be written, linked to yellow color.

    Thanks
    Mirisa
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Color-based conditional equation?

    You can't reference or use color in condition with formula alone. You will need VBA.

    One workaround... if yellow highlight is applied via conditional format. You can use the same/similar logic in formula.

    Few questions...
    Would Expenditure_1 be list of unique values? Or would it contain duplicates?
    If it contains duplicate values, would you consider it to be two separate item in the answer or same?

  3. #3
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    104

    Re: Color-based conditional equation?

    Hi CK76,
    Thanks.
    I have replied your queries but I cannot find my reply.
    So, I am re-writing.

    Expenditure_1 be list is having only unique values (no dups).
    Thanks
    Mirisa

  4. #4
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Color-based conditional equation?

    Mirisage,

    You do not need VBA for this. Try this, should do exactly what your looking for...

    when you click the conditional formatting button, select this workbook as range, create new rule, select bottom option which is to use a formula, in the formula box that appears try this...

    Please Login or Register  to view this content.
    in the box that says "applies to" put this...

    Please Login or Register  to view this content.
    hit apply and your done.


    Now if your actual spreadsheet is different than the one you posted you just need to change some ranges, in the original COUNTIF formula change the $C$4:$C$10 to whatever your second column with data in it is, the $A4 is the first cell in the first column that has data, and lastly the range for the applies to is your first column that you want data to be compared to against the second column of data.

    Hope this helps, Cheers

    Ken

  5. #5
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    104

    Re: Color-based conditional equation?

    Hi Ken,
    Thanks.
    1). I have opened the workbook which contains the values for "Expenditure_1" and "Expenditure_2" variables
    2). Then clicked "conditional Formatting" and then clicked "new rule"
    3). Then clicked the last rule type which is "use a formula to determine which cells to format"
    4). Then in the box named "Format values where this formula is true", I copied and pasted your first formula which is

    =COUNTIF($C$4:$C$10` $A4)>0
    5). Then hit "ok"
    6). Then a message popped up saying "The formula you typed contains an error"

    Could you please see what is the problem?
    Thanks

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Color-based conditional equation?

    Expenditure_1 be list is having only unique values (no dups).
    Sorry, I missed the reply. Ok that makes it easier. But I still need to know what logic is used to apply color to values.

    Without it, it's impossible to construct formula.

    If you are ok with VBA solution (either sub routine or function) it's simple enough.

  7. #7
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37
    Just made it home will look at this shortly.

  8. #8
    Registered User
    Join Date
    01-28-2017
    Location
    US
    MS-Off Ver
    2016
    Posts
    37

    Re: Color-based conditional equation?

    Hi again Mirisage,

    First I need to ask what version of excel you are using, I am running 2016 and I do not get any errors with that formula. I did have an issue or two following my own directions and I appologize for that, lets try again.

    1. Select the cells in the Expenditure column that you want to change colors, so in the example worksheet you provided that would be cells A4 through A10.
    2. Now that they are selected press the conditional formatting button, and click new rule.
    3. Click the last rule type "use a formula to determine which cells to format"
    4. In the box "Format values where this formula is true" paste the following code
    Please Login or Register  to view this content.
    5. Click the format button next to the preview box
    6. In the window that pops up select anything you want the end format to look like. If all you want is highlighting click on the fill tab and pick a color.
    7. After selecting a color click ok, that will close the formatting window and take you back to the rule window, click ok on this window too.

    Thats all, you should now see highlighting in column a on the cells you selected that match the formula.

    If your still having issues I can only think it is because of a different version of excel than mine, or if something else is being added into the formula block like an errant space somewhere. I have attached a working copy of the sheet you submitted. I only had cells A4 through 10 selected as they were the only ones with data, if yours is longer, refer to my first post.

    Ken
    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)

Similar Threads

  1. Add conditional formatting based on color of a cell
    By dreddster in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-12-2015, 04:54 AM
  2. Count / Sum based on Background Color with Manual and Conditional Format Color
    By sam99 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-20-2014, 08:00 PM
  3. Conditional row color based on a cell value
    By WMP in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-12-2012, 02:12 AM
  4. Replies: 5
    Last Post: 05-10-2012, 10:49 PM
  5. Replies: 4
    Last Post: 02-02-2012, 10:11 PM
  6. Conditional sum based on number color
    By jimisom in forum Excel General
    Replies: 6
    Last Post: 02-14-2011, 09:09 PM
  7. Conditional sum based on fill color using VBA
    By airjordan17 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-31-2005, 06:52 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