+ Reply to Thread
Results 1 to 14 of 14

Macro to Conditionally Format Cells based on another cells value

  1. #1
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    311

    Macro to Conditionally Format Cells based on another cells value

    Hi,

    I have a series of worksheets which have in column A7:A1000 a drop down menu that allows the user to select the option of "No". When this happens the entire row (eg. A7:KX7) is then highlighted a different colour via conditional formatting. The formula for Conditional Format is: =ISNUMBER(SEARCH("No",$A7)) and applies to =$A$7:$KX$1000

    Column B (B7:B1000) contains a unique ID number.

    What id like to have is a macro that checks column A in each worksheet for a "No" value. When it finds one (say in A7) it would then search all the other worksheets for the ID number in the cell next to it (eg. B7). It would then highlight each row as per the conditional format above. This basically saver the user having to enter "No" against each ID number in every sheet - as it could occur many times.

    Could anyone help, would be most appreciated.

    Thanks

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Macro to Conditionally Format Cells based on another cells value

    Hi Barking_Mad,

    Can you upload a sample workbook to support your query ? Thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    311

    Re: Macro to Conditionally Format Cells based on another cells value

    Hi,

    The file is large and cant be attached here, so it's in my Dropbox public account, here: https://dl.dropboxusercontent.com/u/...0TRACKING.xlsm

    Hope this works

  4. #4
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    311

    Re: Macro to Conditionally Format Cells based on another cells value

    Bump, please help

  5. #5
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    311

    Re: Macro to Conditionally Format Cells based on another cells value

    To hopefully simplify the request....

    I want a macro to search Column A (A7:A1000) in each worksheet for the value "no". Every time it finds one it should look at the value of the cell in the adjacent Column B (B7:B1000) and then conditional format every row in each worksheet where this newly found value occurs (formatting is: B7:KX7 etc..)

  6. #6
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    311

    Re: Macro to Conditionally Format Cells based on another cells value

    Anyone? :-(

  7. #7
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    311

    Re: Macro to Conditionally Format Cells based on another cells value

    *sniff* Anyone, help would be most appreciated

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Macro to Conditionally Format Cells based on another cells value

    Okay.. see the attached file, change any cell in column A and press Alt + F8 and run macro "DILIPandey" which has below code:-

    Please Login or Register  to view this content.
    FINAL STUDENT TRACKING.zip

    see if and basis your testing, we can improve it

    Regards,
    DILIPandey
    <click on below * if this helps>

  9. #9
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    311

    Re: Macro to Conditionally Format Cells based on another cells value

    Hi,

    Thanks for your help, much appreciated. I cant get it to replicate what already exists in the example you uploaded. When I enter "No" and run the macro it doesnt do anything Also if i remove "No" from the ones highlighted and then run the macro, it doesn't remove the highlighting.

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Macro to Conditionally Format Cells based on another cells value

    When I enter "No" and run the macro it doesnt do anything
    because you already have conditional formatting colors applied there....remove the conditional formatting and then you'll notice that the respective rows are highlighted in yellow I already noticed that.

    Also if remove "no" ....
    This is not asked by you anyways I had already mentioned
    basis your testing, we can improve it



    Regards,
    DILIPandey
    <click on below * if this helps>

  11. #11
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    311

    Re: Macro to Conditionally Format Cells based on another cells value

    Hi,

    I removed the conditional formatting before I tried it, still couldn't get it to work

    Ive copied the code into a new module in my original sheet (and removed Cond Format) and run it from there, still nothing

  12. #12
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Macro to Conditionally Format Cells based on another cells value

    Okay.. post the workbook which you tested - as it is


    Regards,
    DILIPandey
    <click on below * if this helps>

  13. #13
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    311

    Re: Macro to Conditionally Format Cells based on another cells value

    Hi,

    It's telling me it's too big to upload, which is weird as it's no bigger than the one you uploaded! Here it is from my Dropbox account:

    Link

    URL: https://dl.dropboxusercontent.com/u/...ST%20TEST.xlsm

  14. #14
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Macro to Conditionally Format Cells based on another cells value

    Even I faced that issue
    and the approach I used "please zip the file before attaching"



    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

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