+ Reply to Thread
Results 1 to 8 of 8

Conditional formatting if A1 in sheet1 is not = A1 in sheet2 applied to a range

  1. 03-09-2015, 07:25 PM #1
    Mattlawson
    Mattlawson is offline
    Registered User
    Join Date
    03-09-2015
    Location
    NZ
    MS-Off Ver
    2010
    Posts
    4

    Conditional formatting if A1 in sheet1 is not = A1 in sheet2 applied to a range

    I need a conditional formatting formula that will allow this to happen.

    if J4 sheet1 =/=(not equal) H3 sheet2 then highlight yellow
    if J5 sheet1 =/=(not equal) H4 sheet2 then highlight yellow
    if J6 sheet1 =/=(not equal) H5 sheet2 then highlight yellow
    if J7 sheet1 =/=(not equal) H6 sheet2 then highlight yellow
    ........ 20-30 rows

    I can do it one at a time but that is going to take forever.

    Capture.JPG

    I need a formula which will apply to all rows in column J sheet one and column H sheet two.

    Help please.
    Last edited by Mattlawson; 03-09-2015 at 09:10 PM.
    Reply With QuoteRegister To Reply
  2. 03-09-2015, 08:06 PM #2
    ibt
    ibt is offline
    Registered User
    Join Date
    02-28-2015
    Location
    Tennessee, USA
    MS-Off Ver
    2010
    Posts
    25

    Re: Conditional formatting if A1 in sheet1 is not = A1 in sheet2 applied to a range

    Here is a solution with the formula:

    =$A4<>Sheet2!$A3

    conditional formatting is tricky, so if I misinterpreted your question and you need it to do something else or if you need more of an explanation, just let me know.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 03-09-2015, 08:09 PM #3
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Conditional formatting if A1 in sheet1 is not = A1 in sheet2 applied to a range

    Try this...

    1) Highlight the range that you want the conditional formating to highlight
    2) Create a New Rule, choose the Rule Type = 'Use a formula to determine which cells to format'
    3) In the text box, type =J4<>Sheet2!H3
    4) Change the format that you want
    5) OK

    Let me know if this doesn't work...
    Last edited by djapigo; 03-09-2015 at 08:15 PM.
    Reply With QuoteRegister To Reply
  4. 03-09-2015, 08:13 PM #4
    Mattlawson
    Mattlawson is offline
    Registered User
    Join Date
    03-09-2015
    Location
    NZ
    MS-Off Ver
    2010
    Posts
    4

    Re: Conditional formatting if A1 in sheet1 is not = A1 in sheet2 applied to a range

    Your attached file looks like what I want. How do I put that formula into my sheet?

    Thanks
    Reply With QuoteRegister To Reply
  5. 03-09-2015, 08:15 PM #5
    djapigo
    djapigo is offline
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Conditional formatting if A1 in sheet1 is not = A1 in sheet2 applied to a range

    Check the steps I posted...
    Reply With QuoteRegister To Reply
  6. 03-09-2015, 08:26 PM #6
    Mattlawson
    Mattlawson is offline
    Registered User
    Join Date
    03-09-2015
    Location
    NZ
    MS-Off Ver
    2010
    Posts
    4

    Re: Conditional formatting if A1 in sheet1 is not = A1 in sheet2 applied to a range

    Beautiful. Problem solved. Thanks all.
    Reply With QuoteRegister To Reply
  7. 03-09-2015, 08:27 PM #7
    ibt
    ibt is offline
    Registered User
    Join Date
    02-28-2015
    Location
    Tennessee, USA
    MS-Off Ver
    2010
    Posts
    25

    Re: Conditional formatting if A1 in sheet1 is not = A1 in sheet2 applied to a range

    Here it is with it in the exact columns from your example along with some screenshots...

    Guess you figured it out without this, good work!
    Attached Files Attached Files
    Last edited by ibt; 03-09-2015 at 08:28 PM. Reason: OP resolved problem while posting
    Reply With QuoteRegister To Reply
  8. 03-09-2015, 09:10 PM #8
    Mattlawson
    Mattlawson is offline
    Registered User
    Join Date
    03-09-2015
    Location
    NZ
    MS-Off Ver
    2010
    Posts
    4

    Re: Conditional formatting if A1 in sheet1 is not = A1 in sheet2 applied to a range

    There was an extra $ put in when I clicked the cell while inputting the formula which I had to delete. Thanks :D
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. conditional data from Sheet1 populate Sheet2
    By skibumjim in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2014, 03:00 PM
  2. match data of sheet1 and Sheet2 ColumnA, if matched copy columnB of sheet1 to sheet2
    By annielle_09 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-25-2013, 02:58 PM
  3. [SOLVED] sheet1 row range copy to sheet2 firs blank row in same range paste as text ?
    By Zlatko.Pan in forum Excel General
    Replies: 4
    Last Post: 03-17-2013, 05:58 AM
  4. [SOLVED] Copy Range from Sheet2 to Sheet1 through VBA
    By FallingDown in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-29-2012, 02:56 AM
  5. Macro to copy range of cell from sheet1 to sheet2 based on a cell value in sheet2
    By drgwhizz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-25-2012, 10:39 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1