+ Reply to Thread
Results 1 to 11 of 11

How to apply conditional formatting across sheets

  1. #1
    Registered User
    Join Date
    01-26-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2013
    Posts
    93

    How to apply conditional formatting across sheets

    xxxxxxxxxxxxxxxxxx
    Last edited by James__S; 08-24-2015 at 06:24 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to apply conditional formatting across sheets

    1st, pretty much none of your numbers are actually numbers/values, they are text that just looks like a number (just try adding 1 to them, in another cells). You will need to convert them/make them numbers

    2nd, you cannot compare numbers (even after you have converted the text to numbers) to text - which is what you have on sheet2....<1

    Where is this data coming from?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-26-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: How to apply conditional formatting across sheets

    xxxxxxxxxxxxxxxxxx
    Last edited by James__S; 08-24-2015 at 06:23 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to apply conditional formatting across sheets

    Lets take this in stages...

    1. make sure that all your headings match (eg make "Big Bold Red" = "Sea Big Bold Red")

    2. are all your tests being done with the ranges in sheet2? (eg 60-120...38-43 etc) Again, this is text, and would need to somehow be converted to 2 seperate numbers for the comparison

    This is what I have so far...
    to find the value from sheet2...
    =INDEX(SPECIFICATION!$B$7:$E$39,MATCH(PRODUCTS!C$9,SPECIFICATION!$A$7:$A$39,0),MATCH(PRODUCTS!$B10,SPECIFICATION!$B$6:$E$6,0))
    (this is returning <1)
    To convert the "number" in sheet1 to a real number...
    =--SUBSTITUTE(C10,",",".")

    Here is where I am trying to head.

    lets say that Spec sheet A16 was DO, instead of A17, then to do the CF, I would use this formula...
    =INDEX(SPECIFICATION!$B$7:$E$39,MATCH(PRODUCTS!C$9,SPECIFICATION!$A$7:$A$39,0),MATCH(PRODUCTS!$B10,SPECIFICATION!$B$6:$E$6,0))<--SUBSTITUTE(C10,",",".")

  5. #5
    Registered User
    Join Date
    01-26-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: How to apply conditional formatting across sheets

    xxxxxxxxxxxx
    Last edited by James__S; 08-24-2015 at 06:20 PM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to apply conditional formatting across sheets

    2. Yes SPECIFICATION sheet is the reference ranges for the test.
    yes, I got that

    What I meant was - are you testing (where applicable) against cells containg ranges like 60-120 or 38-43. As I said, these are text entries. Would it be possible to split those into 2 seperate cells/columns? In other words, each category in the Spec sheet will have 2 columns

  7. #7
    Registered User
    Join Date
    01-26-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: How to apply conditional formatting across sheets

    xxxxxxxxxxxx
    Last edited by James__S; 08-24-2015 at 06:20 PM.

  8. #8
    Registered User
    Join Date
    01-26-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: How to apply conditional formatting across sheets

    xxxxxxxxxxx
    Last edited by James__S; 08-24-2015 at 06:20 PM.

  9. #9
    Registered User
    Join Date
    01-26-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: How to apply conditional formatting across sheets

    xxxxxxxxxxxx
    Last edited by James__S; 08-24-2015 at 06:20 PM.

  10. #10
    Registered User
    Join Date
    01-26-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: How to apply conditional formatting across sheets

    xxxxxxxxxxxx
    Last edited by James__S; 08-24-2015 at 06:23 PM.

  11. #11
    Registered User
    Join Date
    01-26-2014
    Location
    Sydney
    MS-Off Ver
    Excel 2013
    Posts
    93

    Re: How to apply conditional formatting across sheets

    xxxxxxxxxxx
    Last edited by James__S; 08-24-2015 at 06:22 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] VBA Apply Formatting To Sheets With Specific Name
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-25-2014, 02:01 PM
  2. How to apply conditional formatting to two cols using VBA
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-08-2014, 07:09 AM
  3. [SOLVED] VBA Apply Formatting To Sheets
    By hobbiton73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-20-2013, 01:30 PM
  4. [SOLVED] Add border & apply conditional formatting to sheets based on cell value
    By amar05 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-14-2013, 11:02 AM
  5. [SOLVED] Want to apply conditional formatting
    By mshtuhin in forum Excel General
    Replies: 5
    Last Post: 07-04-2012, 07:41 AM
  6. Replies: 1
    Last Post: 11-12-2010, 03:18 PM
  7. Replies: 2
    Last Post: 09-02-2009, 07:46 AM

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