Duplicates - selected column - highlighting - colour

**SpiNNer12** · 02-07-2019, 06:49 PM

Hello,

I would like to create a macro where user will be able to choose the column symbol, then macro will colour all the duplicates in the column chosen.

I have created such macro but for empty cells. How could I change it for duplicates? Could you please help?

Below is the code for highlighting empty cells after users choice of the column symbol:

Sub empty()
Dim kol As String
Dim ost As Long
ost = Cells(Rows.Count, "A").End(xlUp).Row
 kol = InputBox("Enter column symbol: B, C...etc.", "Column symbol", "B")
 If kol = vbNullString Then
 Exit Sub
 End If
 If IsNumeric(kol) Then
 MsgBox "You entered number, please enter column symbol", vbInformation, "ERROR"
 Exit Sub
 End If
If ost < 5 Then Exit Sub
Range("A5:E" & ost).Interior.Color = xlNone
    Range(Cells(5, kol), Cells(ost, kol)).SpecialCells(xlCellTypeBlanks).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

**alansidman** · 02-07-2019, 06:51 PM

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

**KOKOSEK** · 02-07-2019, 08:06 PM

I've found ut somewhere on my disk. Take a look on it. Should be hint in it:

Sub DuplicateValuesInColumns()
Dim myCell As Range
Dim myRow As Integer
Dim myRange As Range
Dim myCol As Integer
Dim i As Integer
myRow = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Count
myCol = Range(Cells(1, 1), Cells(1, 1).End(xlToRight)).Count
For i = 2 To myCol
 Set myRange = Range(Cells(2, i), Cells(myRow, i))
  For Each myCell In myRange
   If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
      myCell.Interior.ColorIndex = 3
   End If
  Next myCell
Next i
End Sub

Duplicates - selected column - highlighting - colour

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Duplicates - selected column - highlighting - colour

Re: Duplicates - selected column - highlighting - colour

Re: Duplicates - selected column - highlighting - colour

Thread Information

Users Browsing this Thread

Similar Threads

Highlighting selected row/column

[SOLVED] Highlighting row and column of selected cell - revisited

Copy selected colour items from one column to another by vba

[SOLVED] Highlighting text in a column- a different colour for the amount of times it is repeated

Highlighting Lowest Score W/O duplicates Highlighting and Counting player skins

Highlighting selected cells in column B and C

Highlighting the selected row or column

Bookmarks

Bookmarks

Posting Permissions