+ Reply to Thread
Results 1 to 7 of 7

Highlight cell range if 1 particular cell in range contains data

  1. #1
    Registered User
    Join Date
    12-10-2014
    Location
    California, United States
    MS-Off Ver
    2007
    Posts
    12

    Question Highlight cell range if 1 particular cell in range contains data

    Hello,

    Hoping there is a formula or code to do this:

    If cell D3 has data (an 'x' to act like a check mark) then highlight cells B3:H3

    I was able to use conditional formatting to have just that cell (D3) highlight when checked
    But would like to whole cell range to automatically highlight

    Thank you!

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,080

    Re: Highlight cell range if 1 particular cell in range contains data

    Select D3

    Conditional Formatting
    New Rule
    Use a formula to determine...

    =($D$3="x")

    Format as required
    Use Format painter (paintbrush icon) to copy to other cells
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Highlight cell range if 1 particular cell in range contains data

    Okay then try the following steps:-
    1. Select B3:H3. (This would be the range to which Conditional Formatting applies.)
    2. Then Go to Home Tab >> Conditional Formatting >> New Rule >> Formula to determine which cell to format.
    3. Type following formula there.
    =$D3<>""
    4. Select the appropriate highlighting style using Format. Click OK.
    5. Now Put x in D3. B3:H3 should get highlighted.
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  4. #4
    Registered User
    Join Date
    12-10-2014
    Location
    California, United States
    MS-Off Ver
    2007
    Posts
    12

    Re: Highlight cell range if 1 particular cell in range contains data

    This is helpful thank you but in hindsight I did word my question correctly

    I would need cell range to highlight in each row as a mark is put in column D of the corresponding row
    Is there a formula to format the whole sheet at once instead of individual rows?

  5. #5
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Highlight cell range if 1 particular cell in range contains data

    okay then
    1. Select columns B:H
    2. Use Conditional Formatting formula
    =$D1<>""
    instead.

  6. #6
    Registered User
    Join Date
    12-10-2014
    Location
    California, United States
    MS-Off Ver
    2007
    Posts
    12

    Re: Highlight cell range if 1 particular cell in range contains data

    Thanks much!
    Think I just found another way also

    =$D2:D200<>""

  7. #7
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Highlight cell range if 1 particular cell in range contains data

    hmm..!
    but "Applies to" Range should atleast be D2:D200.

    Edit:- In my solution, I have used Dollar($) sign to apply formatting to all the columns in each row of 'Applies to' Range. $ sign has been put before Columns Alphabet (ie. D) which means excel will format all the Columns in each row. In simple words:-
    $D1 means format all columns if criteria($D? <>"")mets .
    D$1 means format all rows for first column in 'Applies to' Range.
    $D$1 means format all rows and columns if criteria ($D$1<>"") mets.
    Last edited by Vikas_Gautam; 03-27-2015 at 01:14 PM.

+ 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. [SOLVED] Formula to count frequency of data in cell range based on data in different cell range
    By jrosko1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2013, 09:47 AM
  2. Highlight the range of selected cell
    By Motilulla in forum Excel General
    Replies: 6
    Last Post: 07-07-2013, 01:51 AM
  3. [SOLVED] Cond. formatting a column/Range, highlight cell in column/range if equal to specific text
    By DFrank231 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2013, 03:35 PM
  4. [SOLVED] Highlight range of cells - run macro - select last cell in range
    By chromachem in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-24-2012, 11:46 AM
  5. [SOLVED] highlight range if cell contains desired data
    By The Other Bollinger in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 03-23-2005, 10:06 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