+ Reply to Thread
Results 1 to 3 of 3

Conditional formatting VBA

  1. #1
    Forum Contributor
    Join Date
    07-21-2018
    Location
    London, England
    MS-Off Ver
    Excel for Mac Version 16.15
    Posts
    117

    Question Conditional formatting VBA

    Hello! I am a VBA newbie and this is my first forum post so go easy on me!

    I need some code to highlight the row between columns B and I light green and change font colour to dark green ("good" style) if the cell in column I contains a tick. I already have the code to do this but I need it to revert to the old formatting if the tick is removed. Ticks are added or removed with a double click (which I have already coded successfully) so maybe the formatting code could be merged into this?


    Conditional formatting code:

    PHP Code: 
    Sub CollectedItemsFormat()
        
    Dim N As LongAs Long
        N 
    Cells(Rows.Count"H").End(xlUp).Row
        
    For 11 To N
            v 
    Cells(i"I").Value
            
    If ChrW(&H2713Then
        Range
    ("B" ":I" i).Style "Good"
            
    End If
        
    Next i
    End Sub 
    Insert/Remove tick code:

    PHP Code: 
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As RangeCancel As Boolean)
        If 
    Not Intersect(TargetRange("I11:I2010")) Is Nothing Then
            Application
    .EnableEvents False
            
    If ActiveCell.Value ChrW(&H2713Then
                ActiveCell
    .ClearContents
            
    Else
                
    ActiveCell.Value ChrW(&H2713)
            
    End If
            
    Cancel True
        End 
    If
        
    Application.EnableEvents True
    End Sub 
    I know this can be done super easy with conditional formatting, and had it set up to do so! However, I have some code that deletes rows depending on certain criteria which messes up the conditional formatting ranges and I can't think of a way to fix that.

    I've attached my work book so you can see what I'm talking about.

    Any help or resources would be appreciated! Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,610

    Re: Conditional formatting VBA

    nevermind.
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    07-21-2018
    Location
    London, England
    MS-Off Ver
    Excel for Mac Version 16.15
    Posts
    117

    Re: Conditional formatting VBA

    Finally found a way to solve this!;


    PHP Code: 
    With Range("B11:I20010")
            .
    FormatConditions.Delete
        End With
        
        Range
    ("B2:I2").Select
        Selection
    .Copy
        Range
    ("B11:I2010").Select
        Selection
    .PasteSpecial Paste:=xlPasteFormatsOperation:=xlNone_
            SkipBlanks
    :=FalseTranspose:=False
        Application
    .CutCopyMode False 
    This code removes any existing conditional formatting and pastes the conditional formatting back to the range (taken from part of the sheet where I know the conditional formatting will never be altered by deleting rows etc.) after my existing code is executed.

    Woohoo! Been trying to find this solution for days.

    Thanks!

+ 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] Override conditional formatting (in general, without changing the conditional formatting)
    By Stormin' in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-21-2017, 07:15 AM
  2. Replies: 1
    Last Post: 12-08-2016, 03:14 PM
  3. Conditional Formatting Removing Previous Conditional Formatting?
    By CravingGod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 01:02 PM
  4. Replies: 6
    Last Post: 01-08-2016, 06:44 PM
  5. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  6. Replies: 1
    Last Post: 09-20-2013, 06:23 PM
  7. Replies: 3
    Last Post: 05-15-2012, 04:13 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