+ Reply to Thread
Results 1 to 4 of 4

Conditional Formatting Based on Range

  1. #1
    Registered User
    Join Date
    03-11-2014
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    30

    Conditional Formatting Based on Range

    Hi,

    I'm trying to write a conditional formatting formula that will shade a cell yellow if its value is contained in a different range of cells.

    For instance, my value in F1 is "ABC"

    I want to know if "ABC" exists in ANY cell A1:A10 or C1:C100.

    If it is in either of these ranges, F1 should turn yellow.

    Can you help? I've tried several things I would expect might work and no luck.

    Thanks,

    David

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Formatting Based on Range

    Try this...

    =COUNTIF(A1:A10,F1)+COUNTIF(C1:C100,F1)

    If the formula returns 0 (meaning the value of cell F1 is not found in either range) then the format will not be applied.

    If the formula returns any number other than 0 (meaning the value of cell F1 can be found in either range) then the format will be applied.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-11-2014
    Location
    Florida
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Conditional Formatting Based on Range

    Quote Originally Posted by Tony Valko View Post
    Try this...

    =COUNTIF(A1:A10,F1)+COUNTIF(C1:C100,F1)

    If the formula returns 0 (meaning the value of cell F1 is not found in either range) then the format will not be applied.

    If the formula returns any number other than 0 (meaning the value of cell F1 can be found in either range) then the format will be applied.
    Perfect, thanks!

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Conditional Formatting Based on Range

    You're welcome. Thanks for the feedback!

+ 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: 5
    Last Post: 11-27-2013, 06:04 PM
  2. Replies: 3
    Last Post: 08-13-2013, 09:44 AM
  3. Conditional Formatting via VBA: Change formatting in range based on value of each cell
    By ralphjmedia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2013, 10:37 AM
  4. Conditional Formatting - Grey Out and Lock A range based on a cell value in that range
    By Excelgnome in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-14-2012, 07:31 PM
  5. Conditional formatting based on date range
    By RGB in forum Excel General
    Replies: 3
    Last Post: 05-23-2006, 12:45 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