+ Reply to Thread
Results 1 to 6 of 6

Colour visible cells in a loop VBA

Hybrid View

hawo12 Colour visible cells in a... 11-23-2014, 11:58 AM
BS Singh Re: Colour visible cells in a... 11-23-2014, 01:21 PM
HaHoBe Re: Colour visible cells in a... 11-23-2014, 01:38 PM
hawo12 Re: Colour visible cells in a... 11-23-2014, 01:41 PM
hawo12 Re: Colour visible cells in a... 11-23-2014, 02:03 PM
mikerickson Re: Colour visible cells in a... 11-23-2014, 02:06 PM
  1. #1
    Registered User
    Join Date
    11-21-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    8

    Colour visible cells in a loop VBA

    Hi, I have a column G of numbers 1 to x. I then have another macro which hides certain rows so that instead of 1,2,3,4 you may get 1,2,3,16. i then want to identify when there is a break in the numbers e.g. 4,5,6,7 15. (a break of more than 2) which should then colour a corresponding cell in column B. Currently it loops every cell i think and therefore doesn't recognise the jump between numbersThanks for your help

    Private Sub CommandButton1_Click()[/SIZE] 
    Dim intRows As Integer 
    Dim intCols As Integer 
    Range("G37").Select 
     
     'would like to use ActiveCell.SpecialCells(xlLastCell).Select but this often changes to a random cell instead of the last visible cell in column G
     
     'determine how many rows and columns to work on
    intRows = Selection.Row 
    intCols = Selection.Column 
     
    intColour = 2 
     
    For i = 2 To intRows 
        For j = 7 To intCols 
             
             'change colour of first cell on each row
            If j = 2 Then Cells(i, 1).Interior.ColorIndex = intColour 
             
            If Not Cells(i, j).EntireRow.Hidden And Not Cells(i, j).EntireColumn.Hidden Then 
                 
                Cells(i, j).Select 
                 
                 'see if value same or different, then colour cell accordingly:
                If Cells(i, j).Value > Cells(i - 1, j).Value + 2 Then 
                    intColour = (intColour + 1) Mod 55 
                     'avoid background colour being same as text (black in this case)
                    If intColour = 1 Then intColour = 2 
                    Cells(i, j - 5).Interior.ColorIndex = intColour ' no selection needed to do this
                Else 
                    Selection.Interior.ColorIndex = Cells(i, j).Interior.ColorIndex ' this still needs to be solved, not sure of your intent
                End If 
                 
            End If 
             
        Next j 
    Next i 
    End Sub

  2. #2
    Forum Contributor
    Join Date
    08-17-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    132

    Re: Colour visible cells in a loop VBA

    Attach a sample file with required output?

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,198

    Re: Colour visible cells in a loop VBA

    Hi, hawo12,
    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    http://www.ozgrid.com/forum/showthread.php?t=192025

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    11-21-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    8

    Re: Colour visible cells in a loop VBA

    Hi, sorry but I posted a query 2 days ago which was unanswered and I really need help, plus for some reason when I try add the attached file I get 'undefined. you do not have permission to perform this action'

  5. #5
    Registered User
    Join Date
    11-21-2014
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    8

    Re: Colour visible cells in a loop VBA

    Solved on apologies for any inconvenience! Shouldn't have cross posted http://www.ozgrid.com/forum/showthre...d=1#post732322

  6. #6
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Colour visible cells in a loop VBA

    There's a good answer at OzGrid.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

+ 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] Have a loop macro loop for all visible worksheets
    By Graeme-Black in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-22-2014, 11:01 AM
  2. Adjust Code to Loop through Visible(filtered) cells only
    By enidnunez in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-19-2013, 09:23 AM
  3. loop in visible cells
    By sal21 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-02-2008, 02:42 PM
  4. Loop in visible cells filtered
    By sal21 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-01-2008, 03:56 PM
  5. [SOLVED] loop on visible cells only
    By Sliman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-26-2006, 09:10 PM

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