+ Reply to Thread
Results 1 to 2 of 2

fomatting cells based on difference between values of cells two ranges.

  1. #1
    Registered User
    Join Date
    10-30-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    52

    fomatting cells based on difference between values of cells two ranges.

    I have data in column 7 of a sheet (rows a and r are defined already, don't worry about them). I want to format the colour of each cell according to it's value. Like normal conditional formatting, I suppose.
    Initially I was happy with this code:

    Please Login or Register  to view this content.
    Now, however, the range values have changed, and I want to compare each cell in column 7 with a corresponding value, found in column 40.

    I don't know how to code it...... i was thinking of something like "if(and( rc40-rc7<10, rc40-rc7=>-10),Then cell.Interior.Color = RGB(255, 255, 255),"")"

    Any ideas?

    I thought about creating a "calculations" sheet for rc40-rc7, but I don't want to slow down the macro.
    I also thought about "For i=a to r, if cells(i,7).value> cells(i,40).value then cell.Interior.Color = RGB(255, 255, 255) Next i" but I worry that will be very slow too.

    xx

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: fomatting cells based on difference between values of cells two ranges.

    Please Login or Register  to view this content.
    I suppose. I don't think coding it how you were thinking with the calculation written as a worksheet formula would be any quicker.

    Edit: I think you would need quite a lot of rows to be concerned about this running slowly - have you turned off screen updating? That might help a bit. (put application.screenupdating = false before and application.screenupdating = true before and after the code that is changing the worksheet.)
    Last edited by ragulduy; 06-05-2013 at 04:13 AM.

+ 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