• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Format cells with repeating values in a similar manner

R

ribena

New Member
Hi all - I am a newbie in terms of posting a question but have been following the forums avidly for some time. My question is this:


In my spreadhseet there are columns of names - many of which are repeated accross the columns. How can I highlight each common item in the same color? For example if the names John and James appear a number of times in different cells, how can I color each instance of "John" one color and "James" another color so you can easily identify all these repeated values. I have played around with fancy conditional formats to no avail. Any help - MUCH appreciated.
 
Hi,


Try this.

Select the range -> Conditional Formatting -> New Rule -> Use Formula

In the space for formula type

=$A2="John"


Format as per the requirement.


Jai
 
thanks - yes - this works when you have a distinct set of repeated values - what I'm looking for is something a little more generic - say for example there are many hundreds of different, duplicated items - how would one create a formula or conditional format that looks to see whether the item is repeated elsewhere within the range - then formats each occurrence of that value the same color - but with many different repeated items and thus many different required colors - ?
 
Hi,


If you have hundreds of different duplicated items, you will need hundreds of different colors to format them.

bit tricky??

Maybe someone can help using VBA.


Jai
 
Good day ribena, Conditional formatting will do this if you select the whole page before you do the formatting, from the CF drop down pick "new formatting rule, then choose "format only cells that contain" "Format only cells with will" "specific text" then "equal to". In the last cell put the name, then click on the "format button" then go wild and do your thing. Remember to select the WHOLE page before you do the above and again when you do the next name. I do not know of a function, formula, or VBA that can guess the names or colours so I think you will have to set up the conditionals yourself.
 
yes - this works but I need to specify each individual repeating value as a separate conditional formatting formula - if there are many hundreds of these and they change from period to period it's not terribly efficient. Trying to think how I can have one conditional format that searches the range for similar items and colors them all (each distinct but similar item) the same - given there will multiple, different repeated items each needing their own color. Hope that makes sense
 
ribena, you keep mentioning "there are many hundreds of different, duplicated items" that is a lot of colours and heaven help any one who is colour blind or has fits if they see a kaleidoscope of colour. :). If a Ninja does help with VBA to auto colour named cells that have not had the names defined then with the data sets you are talking about and as you will have no control over where the colour is as you are running VBA, those that view your spread sheet are going to enter a world of "we all live in a yellow submarine"....but if and when you do achieve this I would love to see an upload with the finished sheet :)
 
haha - yes, I may be a little over-exaggerating when I say HUNDREDS - but there are a large number. I guess my point is more - how can one achieve this efficiently rather than coding for specific instances of repeated items - thanks all
 
Hi Ribena,


In Excel go to VBA > Insert > New Module


Paste the below code there.


It will ask you for the Area where you would like to search similar value.

Just select the whole Column.. for Example Column B

[pre]
Code: 
Sub RepeatingValues()
Dim chkRange As Range
Set chkRange = Application.InputBox("Please select the range where you want to check for Repeating Values", Type:=8)
Set chkRange = Intersect(chkRange, ActiveSheet.UsedRange)
ActiveSheet.UsedRange.Cells.Interior.Color = vbWhite
With chkRange
For i = 1 To .Rows.Count
currentcolor = Int(Rnd() * 100000)
For j = i + 1 To chkRange.Rows.Count
If .Cells(i, 1) = .Cells(j, 1) And .Cells(j, 1).Interior.Color = vbWhite Then
.Cells(i, 1).Interior.Color = currentcolor
.Cells(j, 1).Interior.Color = currentcolor
End If
Next j
Next i
End With
End Sub
[/pre]
For detail, you can download the attahced file..


https://dl.dropbox.com/u/78831150/Excel/Format%20cells%20with%20repeating%20values%20in%20a%20similar%20manner.xlsm


Let us know if you are facing any issue..

Regards,

Deb
 
Deb - that is FANTASTIC - many MANY thanks - I think I can adapt your code to do exactly what I need. Really - so grateful!
 
..just by way of sharing the solution here this is what I ended up using - a variant of the code supplied by Deb for which - huge thanks

[pre]
Code: 
Sub RepeatingValues()

Dim usedcell As Range
ActiveSheet.UsedRange.Cells.Interior.Color = vbWhite

For Each usedcell In ActiveSheet.UsedRange
currentcolor = Int(Rnd() * 100000)
For i = 1 To ActiveSheet.UsedRange.Rows.Count
For j = 1 To ActiveSheet.UsedRange.Columns.Count
If usedcell = Cells(i, j) And Cells(i, j).Interior.Color = vbWhite And Not (usedcell.Row = i And usedcell.Column = j) Then
Cells(i, j).Interior.Color = currentcolor
usedcell.Interior.Color = currentcolor
End If
Next j
Next i
Next usedcell

End Sub
[/pre]
 
Hi Ribena..


WOW.. nice adaptation.. :)

Thank GOD, someone is there to read and edit my coding.. otherwise, in my office programmer told me that "I am a bad Programmer, and they dont even able to understand what it actually doing.. "


You build my confidence.. :)

Thank You...


Regards,

Deb
 
You must log in or register to reply here.
Back
Top