+ Reply to Thread
Results 1 to 13 of 13

conditional formatting based on other cell (over multiple Rows & Column)

  1. #1
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    conditional formatting based on other cell (over multiple Rows & Column)

    Greeting to all Respected

    i am confuse and cant figure-out the solution

    I want to highlight from "AQ14:FH14" down across With Red Shade automatically

    when "SL" text is selected from "D14:AH14" to down across

    i can not do it with conditional formatting with single range formula "=D14="SL". when i use this formula in conditional formatting it only highlight one cell "AQ"

    i required to highlight two cell "AQ & AR"

    plz find the excel sheet file
    conditional formatting based on other cell (over multiple cells).xlsx

    can VB code do it. Or any formula in conditional formation

    any help will be highly appreciated

    Regards

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    This code will work if you remove the hidden columns and place those calculations some place else on your worksheet.

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    thanks for your quick reply.. appreciated

    but i m not allowed to remove the hidden columns and place it some were else as it is our office salary sheet.

    is their any other way around to do so...

    Regards

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    I don't see another option with my skill level. Hopefully, someone else with more advanced VBA will have a solution for you.

  5. #5
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    Quote Originally Posted by alansidman View Post
    I don't see another option with my skill level. Hopefully, someone else with more advanced VBA will have a solution for you.
    Thanks very much appreciated your helpful reply

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    Use of Worksheet_change event could be an option.



    For example.

    Please Login or Register  to view this content.
    See attached example, change the cells in the dropdown.

    This is a macro enabled workbook.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    davesexcel: appreciated your help but when it highlight entire ROW which is hard to search. actually i need to highlight the "AQ14:FH14" down across With Red Shade automatically because when any one enter SL in "D14:AH18" any cell it also highlight its relevant cells in the "AQ14:FH14" to enter time

    hope i am making to understandable

    Regards

  8. #8
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    I'm not sure if you mean Highlight or select, here you go with select, you should be able to figure out highlight if that's what you want.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    Conditional formatting is an option if you are allowed to modify the range: AQ11:FH12 by Unmerging the cells, putting the day and day number in each column. Then, in Aq14: FH18, use:
    Please Login or Register  to view this content.
    notice that starting in column AR11:AR12, alternamte columns are formatted with white font.
    Last edited by protonLeah; 11-07-2015 at 10:46 PM.
    Ben Van Johnson

  10. #10
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    Quote Originally Posted by protonLeah View Post
    nevermind....
    Hmmm..why not?

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    Dave, my original solution (=COUNTIF($D14:$AH14,"SL")>0), formatted the entire row in range AQ14:FH18 if there was any SL in the corresponding row of columns D:AH. The new one only formats cells corresponding to the day with the SL entry. But it requires modifying the cells in AQ11:FH12 which may not be allowed(? ).
    Last edited by protonLeah; 11-07-2015 at 10:57 PM.

  12. #12
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    Quote Originally Posted by protonLeah View Post
    Conditional formatting is an option if you are allowed to modify the range: AQ11:FH12 by Unmerging the cells, putting the day and day number in each column. Then, in Aq14: FH18, use:
    Please Login or Register  to view this content.
    notice that starting in column AR11:AR12, alternamte columns are formatted with white font.

    thats brilliant! This looks promising.. thanks for the solution highly apprecated this exactly what i m trying to achieve.

    davesexcel: i appreciated your help thanks for the feedback everyone.

    cheers everybody

    Best Regards

  13. #13
    Forum Contributor
    Join Date
    06-09-2014
    Location
    Sweden
    Posts
    311

    Re: conditional formatting based on other cell (over multiple Rows & Column)

    Added Reputation and thread marked as solved

+ 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] Conditional Formatting multiple column based on reference cell???
    By inincubus in forum Excel General
    Replies: 2
    Last Post: 01-23-2014, 04:04 PM
  2. [SOLVED] Conditional Formatting of Table Rows based on value in a Column
    By Dominicus in forum Excel General
    Replies: 2
    Last Post: 01-07-2014, 11:14 AM
  3. [SOLVED] Conditional Formatting Rows based on Unique Values in Column
    By drjones5 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-13-2013, 04:27 PM
  4. Replies: 5
    Last Post: 12-28-2012, 02:06 PM
  5. [SOLVED] Conditional Formatting Entire Rows based on Change in Cell Value in Column A
    By Etain in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 09-26-2012, 07:31 AM
  6. Replies: 6
    Last Post: 02-16-2012, 04:52 PM
  7. Replies: 4
    Last Post: 12-17-2011, 06:11 PM
  8. Conditional Formatting Formula to Color Rows Based on Column Value
    By SteveC in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-26-2006, 01:25 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