问excel中带图标集和相对引用的条件格式

EN

=INDIRECT("R14C" & COLUMN(), FALSE)

=OR(TODAY()+7-INDIRECT("R14C" & COLUMN(), FALSE),INDIRECT("R12C" & COLUMN(), FALSE)=100)

Sub CompareIcons()      
   'In this example it starts in cell Q202 and compares to the cell Q210 and does this for 5 rows and 9 columns.  The icons used at xl3Arrows and removes all other conditional formatting on the cells impacted.
    Call GenericIconComparison(Range("q202"), Range("q210"), 5, 9, xl3Arrows, False, False, True)   

'In this example it compares the range from Q202:Y206 to the cells starting in Q210, so in effect the same as the one above
    Call GenericIconComparison(Range("q202:Y206"), Range("q210"), 0, 0, xl3Arrows, False, False, True)  
 

'In this example it does the same as the others, except higher values get a downward arrow and lover values get a higher value
    Call GenericIconComparison(Range("q210"), Range("q202"), 5, 9, xl3Arrows, True, False, True) 
 
End Sub 

Below is the VBA code to implement everything I’ve spoken about.  It does what is says on the tin.
Sub GenericIconComparison(IconsTopleft As Range, CompareTopLeft As Range, Rows As Integer, Cols As Integer, Icons As XlIconSet, ReverseOrder As Boolean, ShowIconsOnly As Boolean, RemoveOtherCondFormatting As Boolean) 
' 
' Icon Comparisons for ranges 
' 
    
    'get column top left for from and too ranges 
    from_col_number = IconsTopleft.Column 
    to_col_number = CompareTopLeft.Column 
    'If a range is given, use that over rows and cols parameters 
    If IconsTopleft.Columns.Count > 1 Then Cols = IconsTopleft.Columns.Count 
    If IconsTopleft.Rows.Count > 1 Then Rows = IconsTopleft.Rows.Count 
    For i = 1 To Cols 
        'get Column letter for from cell 
        from_col = from_col_number + i - 1 
        If from_col > 26 Then 
            col = Chr(64 + Int((from_col - 1) / 26)) + Chr(64 + from_col - Int((from_col - 1) / 26) * 26) 
        Else 
            col = Chr(64 + from_col) 
        End If 
        'get Column letter for comparison cell 
        to_col = to_col_number + i - 1 
        If to_col > 26 Then 
            ToCol = Chr(64 + Int((to_col - 1) / 26)) + Chr(64 + to_col - Int((to_col - 1) / 26) * 26) 
        Else 
            ToCol = Chr(64 + to_col) 
        End If 
        
        'create the rules 
        For j = 1 To Rows 
            'select the cell 
            Range(col + Trim(j + IconsTopleft.Row - 1)).Select 
            'clear other formatting if desired 
            If RemoveOtherCondFormatting = True Then Selection.FormatConditions.Delete 
            'add the rule to compare to other cell 
            Selection.FormatConditions.AddIconSetCondition 
            Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority 
            With Selection.FormatConditions(1) 
                .ReverseOrder = ReverseOrder 
                .ShowIconOnly = ShowIconsOnly 
                .IconSet = ActiveWorkbook.IconSets(Icons) 
            End With 
            With Selection.FormatConditions(1).IconCriteria(2) 
                .Type = xlConditionValueNumber 
                .Value = "=$" + ToCol + "$" + Trim(j + CompareTopLeft.Row - 1) 
                .Operator = 7 
            End With 
            With Selection.FormatConditions(1).IconCriteria(3) 
                .Type = xlConditionValueNumber 
                .Value = "=$" + ToCol + "$" + Trim(j + CompareTopLeft.Row - 1) 
                .Operator = 5 
            End With 
        Next 
    Next 
End Sub