+ Reply to Thread
Results 1 to 10 of 10

Delete Row if the Font RGB is not 0, 0, 255(Blue)

  1. #1
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Delete Row if the Font RGB is not 0, 0, 255(Blue)

    Hello,

    I'm using conditional formatting for column k that turns the cells blue (RGB 0, 0, 255) if the condition is met. I want to be able to delete all other cells where the condition is not met using a macro.

    Any help is much appreciated.

    Thank you,

    - Justin

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Delete Row if the Font RGB is not 0, 0, 255(Blue)

    VBA doesn't recognize cell interior color when you apply conditional formatting. Just an FYI.

  3. #3
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Row if the Font RGB is not 0, 0, 255(Blue)

    Thats what I was afraid of.

    What if I set the same standard to turn the font blue from a macro? Here is the condition:

    Column K is a date

    if the date is today() + 1 or + 2 or + 3 or + 4 then the font should be blue.

    Otherwise nothing needs to happen.

    It wont be sorting through a lot of data 300 rows max.

  4. #4
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Row if the Font RGB is not 0, 0, 255(Blue)

    Okay here is the code I finally figured out how to put together for the coloring of dates (newbie code so if you see a possible improvement I would love to learn). Here is what I got. All I need now is a way to delete all rows that are not colored blue.

    Please Login or Register  to view this content.
    Many thanks to anyone willing to take a look and help me out.

  5. #5
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Delete Row if the Font RGB is not 0, 0, 255(Blue)

    Hi,
    Here's a proposal :

    Please Login or Register  to view this content.
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  6. #6
    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: Delete Row if the Font RGB is not 0, 0, 255(Blue)

    Maybe ...

    Please Login or Register  to view this content.
    EDIT: Too slow!
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Row if the Font RGB is not 0, 0, 255(Blue)

    Quote Originally Posted by GC Excel View Post
    Hi,
    Here's a proposal :

    Please Login or Register  to view this content.
    GC Excel,

    Brilliant! Thank you very much. The case statement is something I need to learn. I will have to practice this.

    Is this method usually faster to execute as well?

  8. #8
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Row if the Font RGB is not 0, 0, 255(Blue)

    Quote Originally Posted by shg View Post
    Maybe ...

    Please Login or Register  to view this content.
    EDIT: Too slow!
    shg,

    Still learning here, but whats the main difference between your method and GC Excel's method?

  9. #9
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Delete Row if the Font RGB is not 0, 0, 255(Blue)

    Is this method usually faster to execute as well?
    From my experience, unless you are using very large data, you won't notice the difference between Select case and If Then Else.
    However, Select Case is easier to read, especially when you have multiple conditions.

    Note :
    There is not much difference between my code and shg's code:
    I use a With state to avoid repeating Cells(irow, "K")
    .Font.ColorIndex is similar to .Font.Color. But with .Font.Color you can use = RGB(x, y, z) while with .ColorIndex you are limited to the 56 colors in the palette
    I use .EntireRow.Delete which is similar to Rows(irow).Delete

    Hope this helps...

  10. #10
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Row if the Font RGB is not 0, 0, 255(Blue)

    Quote Originally Posted by GC Excel View Post
    From my experience, unless you are using very large data, you won't notice the difference between Select case and If Then Else.
    However, Select Case is easier to read, especially when you have multiple conditions.

    Note :
    There is not much difference between my code and shg's code:
    I use a With state to avoid repeating Cells(irow, "K")
    .Font.ColorIndex is similar to .Font.Color. But with .Font.Color you can use = RGB(x, y, z) while with .ColorIndex you are limited to the 56 colors in the palette
    I use .EntireRow.Delete which is similar to Rows(irow).Delete

    Hope this helps...
    Thank you for the explanation. I learned a thing or two.

+ 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. Turn cell A1 blue if any cells between $L$6:$Q$9 are blue
    By sandk0330 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2013, 02:53 PM
  2. Count number of cells that contain red, blue or red and blue
    By kjshearon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-17-2013, 11:26 AM
  3. vba code clear cells by color font dark blue
    By etheer in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-14-2012, 08:29 AM
  4. Replies: 1
    Last Post: 09-23-2006, 05:41 PM
  5. delete row if the font color is blue
    By barkiny in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2005, 07:40 AM

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