+ Reply to Thread
Results 1 to 7 of 7

Colour row but remove conditional formatting

  1. #1
    Registered User
    Join Date
    01-25-2017
    Location
    Worcester
    MS-Off Ver
    2016
    Posts
    93

    Colour row but remove conditional formatting

    Hi,

    The below code is used to Search for a number in a textbox and if found on another sheet it will strikethrough the line and also colour it red.

    In addition to this I have conditional formatting that colours lines green if there are any value in columns A, E, L and P but the above VBA doesn't work on rows where this conditional formatting has already confirmed those cells unless I remove an entry from one of the cells.

    Is there a way for the VBA to instruct the conditional formatting to ignore these conditions OR when the number is searched and found in the other sheet, it removes the value from column P (as this isn't needed if we are at this point) allowing the strikethrough and row colouring to take affect?

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    554

    Re: Colour row but remove conditional formatting

    Please Login or Register  to view this content.
    will remove conditional formatting from a specified range.

  3. #3
    Registered User
    Join Date
    01-25-2017
    Location
    Worcester
    MS-Off Ver
    2016
    Posts
    93

    Re: Colour row but remove conditional formatting

    Thanks BellyGas, but how do I use this in the script I posted?

  4. #4
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    554

    Re: Colour row but remove conditional formatting

    straight after the 'set rng.....' put range(rng).clearformats

  5. #5
    Registered User
    Join Date
    01-25-2017
    Location
    Worcester
    MS-Off Ver
    2016
    Posts
    93

    Re: Colour row but remove conditional formatting

    As in the below? This gives me an error

    Capture.PNG

    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    03-24-2014
    Location
    England
    MS-Off Ver
    Excel 2003 - 2016
    Posts
    554

    Re: Colour row but remove conditional formatting

    My bad:

    Please Login or Register  to view this content.
    maybe put it inside the if range is nothing statement as it'll error if fnd wasnt found.

  7. #7
    Registered User
    Join Date
    01-25-2017
    Location
    Worcester
    MS-Off Ver
    2016
    Posts
    93

    Re: Colour row but remove conditional formatting

    Not a problem. So I've now placed this within the IF range is nothing statement and it partially works.

    Please Login or Register  to view this content.
    It is now striking through the line but it remains green, see screen shot. I need it to ignore the colour formatting too and change it to pink like the ones above.

    Capture.PNG

+ 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] Conditional formatting with colour
    By Onditi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-31-2016, 06:03 AM
  2. Replies: 6
    Last Post: 04-11-2016, 09:48 AM
  3. [SOLVED] conditional formatting can not keep original colour
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-27-2014, 01:33 PM
  4. Colour Row with Conditional Formatting
    By trhorsley in forum Excel General
    Replies: 4
    Last Post: 08-24-2011, 10:47 AM
  5. Conditional formatting with colour
    By ldavison10 in forum Excel General
    Replies: 9
    Last Post: 08-12-2009, 01:10 PM
  6. Conditional Formatting Fill colour with weekday colour
    By nattasiray in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-17-2008, 06:49 AM
  7. Help with conditional formatting:colour code
    By Vipulparbat in forum Excel General
    Replies: 1
    Last Post: 08-30-2006, 08:53 AM

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