Highlight cells if value appears at least n times
This tutorial shows how to highlight cells if the contained in the cells appear at least n times in a selected range using Excel or VBA
Select range > Home tab > Style group > Click on Conditional Formatting > New Rules > Select Use a formula to determine which cells to format > Enter formula > Click on Format > Fill tab > Select color > Click OK
| 1. Select the range in which you want to highlight cells if the value appears at least n times. Note: in this example we are selecting range B3:C12. |
 |
| 2. Select the Home tab. |  |
| 3. Click on Conditional Formatting in the Style group. 4. Click on New Rules. |
 |
| 5. Select Use a formula to determine which cells to format. 6. Enter the following formula =COUNTIF($B$3:$C$12,B3)>=3. 7. Click on the Format button. Note: it's important that the cell referenced in the COUNTIF formula refers to the active cell in the selection. In this example that cell is B3. |
 |
| 8. Select the Fill tab. 9. Select a color. 10. Click OK. |
 |
| 11. Click OK. |  |
| Highlighted cells with the value that has been duplicated at least three times in the selected range. |  |
METHOD 1. Highlight cells if value appears at least n times
VBA
Sub Highlight_cells_if_value_appears_at_least_n_times()
'declare variables
Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range
Dim ws As Worksheet
Dim ColorRng As Range
Dim ColorCell As Range
Set ws = Worksheets("Analysis")
Set ColorRng = ws.Range("B3:C12")
Set ColorRng = ws.Range("B3:C12")
'highlight cells that have a value that is duplicated at least three times in the selected range
For Each ColorCell In ColorRng
For Each ColorCell In ColorRng
If WorksheetFunction.CountIf(ColorRng, ColorCell.Value) >= 3 Then
ColorCell.Interior.Color = RGB(220, 230, 241)
Else
ColorCell.Interior.ColorIndex = xlNone
End If
ColorCell.Interior.Color = RGB(220, 230, 241)
Else
ColorCell.Interior.ColorIndex = xlNone
End If
Next
End Sub
ADJUSTABLE PARAMETERS
Worksheet Selection: Select the worksheet which contains the data in which you want to highlight cells by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Highlight Range: Select the range in which to highlight cells by changing the range ("B3:C12") in the VBA code.
Highlight Color: Select the color to highlight the cells that contain a value n amount of times in the selected range by changing the RGB code (220, 230, 241) in the VBA code.
n Duplicate: Select the number of times a value is to appear in cells for the cells to be highlighted by changing number 3 in the VBA code.
Worksheet Selection: Select the worksheet which contains the data in which you want to highlight cells by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Highlight Range: Select the range in which to highlight cells by changing the range ("B3:C12") in the VBA code.
Highlight Color: Select the color to highlight the cells that contain a value n amount of times in the selected range by changing the RGB code (220, 230, 241) in the VBA code.
n Duplicate: Select the number of times a value is to appear in cells for the cells to be highlighted by changing number 3 in the VBA code.
ADDITIONAL NOTES
Note 1: In this example the VBA code will highlight cells if a value is repeated three times, which is represented by (>=3) in the VBA code.
Note 1: In this example the VBA code will highlight cells if a value is repeated three times, which is represented by (>=3) in the VBA code.
EXPLANATION
This tutorial shows how to highlight cells if the value contained in the cells appear at least n times in a selected range using Excel and VBA.
This tutorial provides one Excel method that can be applied to highlight cells if value appears at least n times in a selected range by using the Conditional Formatting. This is accomplished in 11 steps.
This tutorial provides one VBA method that can be applied to highlight cells if value appears at least n times. In this example the VBA code will loop through each cell in the nominated range and will highlight each cell that contains a value that appears at least three times in a selected range.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Highlight cells if value appears no more than n times | How to highlight cells if the value contained in the cells appear no more than n times in a selected range | |
| Highlight cells with duplicate values | How to highlight cells that contain duplicate values in a selected range | |
| Color blank cells | How to highlight blank cells | |
| Color non blank cells | How to highlight non blank cells | |
| Highlight cells with unique values | How to highlight cells that contain unique values in a selected range |