+ Reply to Thread
Results 1 to 10 of 10

VBA. Change cell interior colour for greater visibility

  1. 05-15-2012, 06:53 PM #1
    rain4u
    rain4u is offline
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    VBA. Change cell interior colour for greater visibility

    Hi I'm not sure if its even possible to have a macro that I'm after but here we go.
    In column E (last row as per col E), I have some order numbers as below:

    Please Login or Register  to view this content.
    As you can notice some of the order numbers are repeated. This is normal on my spreadsheet. If there are repeated numbers they normally are all in straight order and aren't scattered. Lets say order 200053133. As you can see one is on row 4 while next one follows on row 5. So normally they won't be scattered around like one is on row 4 and next time its on row 25, etc. This should be the case 100% of the time. Now what I would like to achieve is that there would be 2 different colours where VBA would change the cell interior color so they would swap after each order number to make things more visible. In a similar fashion as below:

    Please Login or Register  to view this content.

    If possible I would prefer to have VBA solution as I have some other ideas for conditional formating. But if its not possible with VBA but is an option with conditional formation, then I could live with it.

    I have attached spreadsheet with sheets "before" and "after".


    As always I'm very grateful of any help.
    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-15-2012, 07:11 PM #2
    JieJenn
    JieJenn is offline
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: VBA. Change cell interior colour for greater visibility

    Try

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Learn Data Analysis or Excel youtube.com/jiejenn
    Reply With QuoteRegister To Reply
  3. 05-15-2012, 07:41 PM #3
    AlvaroSiza
    AlvaroSiza is offline
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: VBA. Change cell interior colour for greater visibility

    Quote Originally Posted by JieJenn View Post
    Try
    Failed. Not OP's request.

    Good puzzle. I could not solve it on my first go around but might give it a go a bit later. It needs a toggle to know whether after a repeat trend is found, it is on teal or grey.
    Perhaps it was the Noid who should have avoided me...
    If you are satisfied with my solution click the small star icon on the left. Thanks
    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
    3. Paste the code from above into the empty white space. Close the VBE.
    4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".
    Reply With QuoteRegister To Reply
  4. 05-15-2012, 07:47 PM #4
    rain4u
    rain4u is offline
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. Change cell interior colour for greater visibility

    This code actually works:
    Please Login or Register  to view this content.
    Awesome stuff. May I ask what do I have to change to make this work for with column 5 (i.e. col E).

    I tried to play around with it but had no joy. I'm still such a newbie.
    Cheers
    Reply With QuoteRegister To Reply
  5. 05-15-2012, 07:51 PM #5
    AlvaroSiza
    AlvaroSiza is offline
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: VBA. Change cell interior colour for greater visibility

    Based on what you proposed in the 'After' worksheet of your attachment, this code will not do what you are after. Jie's code, while useful, simply alternates the text colors, without reference to patterns or trends.

    If you would like to see it work in Column E, switch all instances of 
    Please Login or Register  to view this content.
    to 
    Please Login or Register  to view this content.
    Last edited by AlvaroSiza; 05-15-2012 at 07:53 PM.
    Reply With QuoteRegister To Reply
  6. 05-15-2012, 07:57 PM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: VBA. Change cell interior colour for greater visibility

    How about just conditional formatting? If the numbers start in A2,

    =ROUND(MOD(SUMPRODUCT(1/COUNTIF(A$2:A2, A$2:A2)), 2), 0)
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  7. 05-15-2012, 08:13 PM #7
    rain4u
    rain4u is offline
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. Change cell interior colour for greater visibility

    Right I have to apologize. On my attached example file there was a mistake with 104151042 (it was wrongfully highlighted). See what happens when you do things manually.
    Anyway I used JieJenn code and changed it to:

    Please Login or Register  to view this content.
    This changes the interior color rather than font. Anyway, I attached the updated examples as below for both Col A and Col E if anyone is interested.

    For me it does the job.

    Thank you for everyone.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 05-15-2012, 09:56 PM #8
    Sa DQ
    Sa DQ is offline
    Forum Contributor
    Join Date
    03-21-2012
    Location
    Ho Chi Minh city
    MS-Off Ver
    Excel 2003
    Posts
    180

    Re: Try the following macro

    PHP Code: 
    Option Explicit   'S3'

    Sub MyBackColor()
         Dim Rng As Range, Cls As Range
     Dim MyNum     As Long, MyColor As Long
         Const MyC As Byte = 35
     
         For Each Cls In Range([E2], [E2].End(xlDown))
        If     Cls.Value <> MyNum Then
            MyColor     = MyColor + 1
            MyNum     = Cls.Value
        End     If
            Cls.Interior.ColorIndex = MyC + (MyColor Mod 2)
         Next Cls
    End Sub 
    Last edited by Sa DQ; 05-15-2012 at 09:58 PM.
    Reply With QuoteRegister To Reply
  9. 05-16-2012, 02:30 PM #9
    rain4u
    rain4u is offline
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA. Change cell interior colour for greater visibility 

    Please Login or Register  to view this content.
    This code works in terms of highlighting the correct cells but the only thing I couldn't figure out is how you can choose or set the color index for the two colors. I understand that section "Const MyC As Byte = 35" defines one color but how to choose or set second color, lets say color index 14?


    Thank you for your version.
    Reply With QuoteRegister To Reply
  10. 05-17-2012, 03:36 AM #10
    Sa DQ
    Sa DQ is offline
    Forum Contributor
    Join Date
    03-21-2012
    Location
    Ho Chi Minh city
    MS-Off Ver
    Excel 2003
    Posts
    180

    Re: VBA. Change cell interior colour for greater visibility 

    Please Login or Register  to view this content.
    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)

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