+ Reply to Thread
Results 1 to 5 of 5

conditionally formatting a range using VB

Hybrid View

rodk conditionally formatting a... 02-18-2008, 09:39 AM
antoka05 You can try to use this... 02-18-2008, 10:33 AM
rodk Alternative 02-18-2008, 12:33 PM
antoka05 Remove this line to fix the... 02-19-2008, 04:59 AM
royUK rodk Please read the Forum... 02-19-2008, 05:34 AM
  1. #1
    Registered User
    Join Date
    02-18-2008
    Location
    Pennsylvania, USA
    Posts
    2

    conditionally formatting a range using VB

    I recorded a macro to conditionally format a row of numbers highlighting the minimum non-zero number in the row. That worked fine. I modified the vb code to do the same thing for a range of rows. I assigned the macro to ctrl-f. When I press ctrl-f, the macro runs and highlights the zeros rather than the minimum non-zero numbers. But, when I highlight the range, call up the conditional format and click OK, the conditional format does what I intended it to do. Can somebody help me? I have pasted the VB code below. Notice that I commented out the original macro that was recorded.
    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 2/18/2008 by rodk
    '
    ' Keyboard Shortcut: Ctrl+f
    
        Dim x As Integer
        Dim sx As String
        Dim forumla As String
        
        For x = 1 To 2 Step 1
            sx = CStr(x)
            Range("a" & sx & ":f" & sx).Select
            On Error Resume Next                'in case the range doesn't have a cf
            Selection.FormatConditions.Delete
            Formula = "=a" & sx & "=min(if($a$" & sx & ":$f$" & sx & "<>0," & _
                "$a$" & sx & ":$f$" & sx & "))"
            Selection.FormatConditions.Add Type:=xlExpression, Formula1:=Formula
            Selection.FormatConditions(1).Interior.ColorIndex = 6
        Next x
    
        'ORIGINAL RESULTS FROM THE MACRO I RECORDED    
        'Range("A1:F1").Select
        'Selection.FormatConditions.Delete
        'Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        '    "=A1=MIN(IF($A$1:$F$1<>0,$A$1:$F$1))"
        'Selection.FormatConditions(1).Interior.ColorIndex = 6
        'Range("A2").Select
    End Sub
    Last edited by royUK; 02-19-2008 at 05:35 AM.

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    You can try to use this macro:
        rowsNum = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
        For r = 1 To rowsNum
          minVal = Evaluate("MIN(IF(" & r & ":" & r & ">0," & r & ":" & r & "))")
    
          With ActiveSheet.Rows(r)
             .Interior.ColorIndex = xlNone
             Set found = .Find(minVal, LookIn:=xlValues)
             If Not found Is Nothing Then
                firstAddress = found.Address
                Do
                   DoEvents
                   
                   If Val(found.Value) = Val(minVal) Then
                      found.Interior.ColorIndex = 6
                      Exit Do
                   End If
                   Set found = .FindNext(found)
                Loop While Not found Is Nothing And found.Address <> firstAddress
             End If
          End With
       Next
    Regards,
    Antonio

  3. #3
    Registered User
    Join Date
    02-18-2008
    Location
    Pennsylvania, USA
    Posts
    2

    Alternative

    Antonio,

    Your solution worked (mostly). If there are two equivalent non-zero minimum amounts, it will only highlight one.

  4. #4
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628
    Remove this line to fix the problem:
    Exit Do
    Regards,
    Antonio

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    rodk

    Please read the Forum Rules & use Code tags in future. I will add them this time, if you click on Edit in your post you will see what needs doing in future.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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