dcsimg


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Conditional Formatting Multiple ‘IF Then’s With Specified Colors

  1. #1
    Join Date
    Oct 2010
    Posts
    1

    Conditional Formatting Multiple ‘IF Then’s With Specified Colors

    Quite simply, I would like to sort and filter names and dates (in specified colors given the specified dates).

    What has taken me four days to figure out will likely take someone in this forum seconds.
    I am very new to all of this, and I come modestly, respectfully, and appreciative for any help.
    I have a few questions, but they are all connected, and with a few short answers I can have my entire sheet functioning beautifully.

    Conditional formatting is the most simple way to do this. However, I have a =ColorFunction sub that conditional formatting would interfere with (because it is my understanding the “=ColorFunction” does not recognize colors highlighted conditionally (or with private subs either). For this reason, I have thrown together an alternative sub macro that creates the same result that my colorfunction formula could recognize.

    <Sub Highlight_Date_Today_Red()
    'searches finds and highlights today date in range in a specified color without the use of standard conditional formatting
    ' Highlight_Date_Today_Red Macro

    Range("E4:E1000").Select
    Application.FindFormat.Clear
    'On Error Resume Next
    Cells.Find(What:=DateValue(Today), After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate

    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    End Sub>

    It worked but it only highlighted one cell. Sad for me, and yes it is okay to laugh at my code (but at least give me credit for attempting this Frankenstein monstrosity) Lol

    Here are my four questions:

    How do I get it to highlight the current date today in the entire row accordingly?

    As for multiple values, how would I get it to highlight yesterday’s date in another the color the same way accordingly?

    Next, how would I write this same code with something along the lines of:

    If Range(“H4”) contains “/”, then highlight cell (“E4”) in this color?

    Finally,

    If Range(“H4”) contains no fill color, then highlight cell (“E4”) in this color?


    Any help on this would be super extremely appreciated you have no idea. I tried so many variations from so many forums for so long that it’s time I sought help from someone. Anyone who can help me this will have a speedy response from me guaranteed. I will be monitoring any responses round the clock. Thanks in advance.

    Quick note: I did make sure the dates I attempted to find and highlight were formatted as “date cells” and not “text cells”.

  2. #2
    Join Date
    Oct 2010
    Posts
    22

    Excel

    Please note I've never used Excel, that being said, try this.
    Code:
    Public Sub xFind()
    Dim xLoop As Integer
    Dim xRng As Range
    Dim i As Integer
    Dim xToDay As String
    
    xToDay = "06/23/2010"
    xLoop = WorksheetFunction.CountIf(Columns(1), DateValue(xToDay))
    
    Set xRng = Range("A1")
     For i = 1 To xLoop
    
      Set xRng = Columns(1).Find(What:=DateValue(xToDay), After:=xRng, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
    
         xRng.Font .Bold = True
          xRng.Font.Color = 255
    
     Next i
     
    End Sub

Similar Threads

  1. Conditional Formatting / Macros
    By Richard WJ in forum VB Classic
    Replies: 0
    Last Post: 03-13-2007, 10:44 AM
  2. Replies: 4
    Last Post: 02-23-2006, 04:57 PM
  3. Replies: 1
    Last Post: 07-31-2002, 02:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center