[已解决]请高手帮忙看看，有错在哪里，为何相同内容的不能显示红色

jackcat

Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
i = ActiveSheet.Cells(65536, ActiveSheet.UsedRange.Column).End(xlUp).Row
j = ActiveSheet.Cells(ActiveSheet.UsedRange.Row, 256).End(xlToLeft).Column
UsedRange.Select
If WorksheetFunction.CountIf(Range(Cells(1, 1), Cells(i, j)), Cells(1, 1)) > 1 Then
  Selection.Font.ColorIndex = 3
End If
End Sub

最佳答案

月排行榜 / 总排行榜

hwc2ycy

2012-8-23 23:57

1. 
   
2. Private Sub CommandButton1_Click()
   
3.      Dim i As Integer
   
4.      Dim j As Integer
   
5.      Dim myrow&
   
6.      Dim mycol&
   
7.      
   
8.      Application.ScreenUpdating = False
   
9.      i = ActiveSheet.Cells(65536, ActiveSheet.UsedRange.Column).End(xlUp).Row
   
10.      j = ActiveSheet.Cells(ActiveSheet.UsedRange.Row, 256).End(xlToLeft).Column
    
11.      ActiveSheet.UsedRange.Font.ColorIndex = 0
    
12.      UsedRange.Select
    
13.      For myrow = ActiveSheet.UsedRange.Row To i
    
14.         For mycol = ActiveSheet.UsedRange.Column To j
    
15.             If WorksheetFunction.CountIf(Range(Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Column), Cells(i, j)), Cells(myrow, mycol)) > 1 Then
    
16.                 Cells(myrow, mycol).Font.ColorIndex = 3
    
17.             End If
    
18.         Next
    
19.     Next
    
20.     Application.ScreenUpdating = True
    
21. End Sub
    
22. 
    

复制代码

跳转到最佳答案楼层

hwc2ycy

1. 
   
2. Private Sub CommandButton1_Click()
   
3.      Dim i As Integer
   
4.      Dim j As Integer
   
5.      Dim myrow&
   
6.      Dim mycol&
   
7.      
   
8.      Application.ScreenUpdating = False
   
9.      i = ActiveSheet.Cells(65536, ActiveSheet.UsedRange.Column).End(xlUp).Row
   
10.      j = ActiveSheet.Cells(ActiveSheet.UsedRange.Row, 256).End(xlToLeft).Column
    
11.      ActiveSheet.UsedRange.Font.ColorIndex = 0
    
12.      UsedRange.Select
    
13.      For myrow = ActiveSheet.UsedRange.Row To i
    
14.         For mycol = ActiveSheet.UsedRange.Column To j
    
15.             If WorksheetFunction.CountIf(Range(Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Column), Cells(i, j)), Cells(myrow, mycol)) > 1 Then
    
16.                 Cells(myrow, mycol).Font.ColorIndex = 3
    
17.             End If
    
18.         Next
    
19.     Next
    
20.     Application.ScreenUpdating = True
    
21. End Sub
    
22. 
    

复制代码

hwc2ycy

代码重新帮你改了下。你的思路方向是对的。可是得没有考虑到每个单元格，还有就是COUNTIF的条件使用。

hwc2ycy

程序排错的过程中，多运用F8,立即窗口与DEBUG。

jackcat

hwc2ycy 发表于 2012-8-24 00:22
程序排错的过程中，多运用F8,立即窗口与DEBUG。

非常感谢高手指导！谢谢！

zm0115

感觉楼主问题考虑的过于复杂了，如下代码就可以完成了，如果是为了学习usedrange则另当别论。

1. Sub aa()
   
2. Dim r As Range, rng As Range
   
3. Set rng = ActiveSheet.UsedRange
   
4. For Each r In rng
   
5.     r.Interior.ColorIndex = IIf(r <> "" And WorksheetFunction.CountIf(rng, r) > 1, 3, -4142)
   
6. Next
   
7. End Sub

复制代码

hwc2ycy

zm0115 发表于 2012-8-24 09:04
感觉楼主问题考虑的过于复杂了，如下代码就可以完成了，如果是为了学习usedrange则另当别论。

for each确实比较简单。