+ Reply to Thread
Results 1 to 3 of 3

Cell border created with macro seems to be fixed to cell =ignores filter, does not move

  1. #1
    Registered User
    Join Date
    02-13-2023
    Location
    Slovakia
    MS-Off Ver
    Office 365
    Posts
    2

    Cell border created with macro seems to be fixed to cell =ignores filter, does not move

    Dear all,

    A tried to substitute conditional formatting function by writing a code. It seemed to be working, except one but substantial issue. After sorting the columns the cell border does not move with the original cell values. The CellBorder remains on the original cell/place, however the cell fill color seems to be moving. Is there something that could stabilize match of the border and the cellvalue?

    Thank you!
    Veronika

    used code:

    Sub conditional_formatting_on_metrics()
    Dim xCell As Range
    Dim CommentValue As String
    Dim CommentRange As Range

    Set CommentRange = Range("G:G")
    For Each xCell In CommentRange
    CommentValue = xCell.Value
    Select Case CommentValue
    Case "Red"
    xCell.Interior.Color = RGB(192, 0, 0)
    Case "LightGreen"
    xCell.Interior.Color = RGB(226, 239, 218)
    Case "Green"
    xCell.Interior.Color = RGB(112, 173, 71)
    End Select

    Next xCell
    Set CommentRange = Range("H:I")
    For Each xCell In CommentRange
    CommentValue = xCell.Value
    Select Case CommentValue
    Case "Red"
    xCell.Interior.Color = RGB(192, 0, 0)
    Case "Red Warning"
    xCell.Interior.Color = RGB(237, 125, 49)
    xCell.Borders.LineStyle = Excel.XlLineStyle.xlContinuous
    xCell.Borders.Color = RGB(192, 0, 0)
    Case "Amber Warning"
    xCell.Interior.Color = RGB(226, 239, 218)
    xCell.Borders.LineStyle = Excel.XlLineStyle.xlContinuous
    xCell.Borders.Color = RGB(237, 125, 49)
    Case "Green"
    xCell.Interior.Color = RGB(112, 173, 71)
    End Select

    Next xCell

    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,545

    Re: Cell border created with macro seems to be fixed to cell =ignores filter, does not mov

    I'm afraid Excel has that. It moves the fill colors, but not the border.
    The solution in the attachment is to add an volatile formula (DATE(), NOW(), RAND() or other) in the worksheet, which will cause a recalculation every time the worksheet changes, i.e. triggering the _Calculate event. And the event is designed to repaint the range.
    In the code, I added a Stop command so that you can see how often the _Calculate event is triggered.
    You can hide the volatile formula by hiding the entire column or formatting this one cell using a custom formatting code: ;;;
    If the workbook contains many worksheets that contain formulas, the _Calculate event will trigger very often. Therefore, the condition in this event will probably need to be more elaborate, so that the repainting of the sheet only happens at the appropriate time, rather than continuously.

    Artik
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-13-2023
    Location
    Slovakia
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Cell border created with macro seems to be fixed to cell =ignores filter, does not mov

    Thank you Artik for your confirmation of what I have been suspicious of ("Excel has it"). As I have to spread the file on other I decided to go without borders. Thank you for your time and effort, very appreciated.

+ 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] Filter and transfer fixed number to created sheet
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-14-2016, 01:27 AM
  2. Macro to Move Files from a Folder to Newly created folders based on value in a cell
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-08-2015, 08:04 PM
  3. [SOLVED] Excel sort alphabetically doesn't move cell border
    By Marco-Kun in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-25-2013, 11:13 AM
  4. Excel sort alphabetically doesn't move cell border
    By Marco-Kun in forum Excel General
    Replies: 0
    Last Post: 04-18-2013, 08:21 AM
  5. [SOLVED] Macro to add a border if cell is not blank
    By stonefish150 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-03-2012, 12:52 PM
  6. Filter Pivottable on a fixed referenced cell.
    By basima in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-02-2012, 01:47 PM
  7. Replies: 1
    Last Post: 03-05-2011, 12:37 PM

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