Archive for the ‘Formatting’ category

How to find and color duplicate values with Excel and VBA

December 15th, 2009

The obvious answer to the above question could be “by using conditional formatting, fool!” That’s true.  We could easily write a formula checking if the value just entered is a duplicate and, if it’s true, change accordingly the cell background color. We could ….. but

… what I really want is to use the same color for each duplicated value. And at the same time, use a different color for different values. It might sound complicated, but is not and the animation below should settle all the doubts.

How to find and color duplicate values with Excel and VBA

I also like the idea of having an option of easily changing the colors. To be honest, I don’t have a clue about how I could accomplish it with conditional formatting only.  And since I don’t have a clue I decided to use VBA.

In the first step I prepared a list of colors I want to use. It’s nothing, but a range with colored cells (we simply use Excel formatting).

How to find and color duplicate values with Excel and VBA

We can easily choose and change colors for duplicate values

Every time our macro finds a duplicate value in a chosen column, it will get the color from one of previously colored cells (our color list mentioned above).  Starting from the top one and moving down. When it reaches the end it will – surprise, surprise :) – start from the beginning.

Because I want my macro to run and refresh cell colors every time I change a value in a selected row I assigned the macro to Excel onChange event. And finally, my macro:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngKolory As Range

Dim rngDoPokolorowania As Range

Dim LicznikKolorow As Integer

Dim Licznik As Integer

Dim rngKolumna As Range

Dim rngDaneWypelnione As Range

' cells with colors to choose from

Set rngKolory = wksKolory.Range("rngKoloryStart").Resize(wksKolory.Range("settIleKolorow").Value, 1)

' cells with data to be "colored"

Set rngDoPokolorowania = wksDane.Range(Range("rngDaneStart"), Cells(65535, Range("rngDaneStart").Column).End(xlUp))

' column with data

Set rngKolumna = Columns("B")

With wksDane

Set rngDaneWypelnione = .Range(.Range("rngDaneStart"), .Range("rngDaneStart").Offset(10000).End(xlUp))

End With

If Not Intersect(Target, rngKolumna) Is Nothing Then

Application.ScreenUpdating = False '

' Let's clear the whole data area (set background color to default)

rngDaneWypelnione.Resize(rngDaneWypelnione.Count + 1).Interior.ColorIndex = _

wksKolory.Range("rngDomyslneTlo").Interior.ColorIndex

LicznikKolorow = 1 ' color counter reset

With rngDoPokolorowania

' first cell

If Application.WorksheetFunction.CountIf(rngDoPokolorowania, .Cells(1).Value) > 1 Then

.Cells(1).Interior.ColorIndex = rngKolory.Cells(LicznikKolorow).Interior.ColorIndex

LicznikKolorow = LicznikKolorow + 1

If LicznikKolorow > rngKolory.Count Then LicznikKolorow = 1

End If

'more than one cell

If rngDaneWypelnione.Count > 1 Then

' for following cells

For Licznik = 2 To .Count

If Application.WorksheetFunction.CountIf(rngDoPokolorowania, _

.Cells(Licznik).Value) > 1 Then

If Application.WorksheetFunction.CountIf(Range("rngDaneStart").Resize(Licznik - 1), .Cells(Licznik).Value) > 0 Then

.Cells(Licznik).Interior.ColorIndex = _

rngDaneWypelnione.Find(what:=.Cells(Licznik).Value, after:=.Cells(Licznik), SearchDirection:=xlPrevious, lookat:=xlWhole).Interior.ColorIndex

Else

.Cells(Licznik).Interior.ColorIndex = rngKolory.Cells(LicznikKolorow).Interior.ColorIndex

LicznikKolorow = LicznikKolorow + 1

If LicznikKolorow > rngKolory.Count Then LicznikKolorow = 1

End If

End If

Next Licznik

End If

End With

Application.ScreenUpdating = True

End If

End Sub

This might not be the most optimal solution, but in simple cases will play it’s role just fine.

Marcin

Unusual Excel chart

November 12th, 2009

Today we are going to create a little unusual, but quite natty chart in Excel.  Image below shows how it works.Unusual Excel chart

There is no VBA involved and the whole idea is based on the usage of conditional formatting only.

Before we start writing proper formulas controlling the way our chart is going to look, let’s prepare “the stage”. What we need to do is to prepare an area of 100 (10 rows and 10 columns), preferably square, excel cells (unless you have a better idea about how it could look like).

Unusual Excel chart

Our basic “stage” is now set and I am quite happy about cells sizes. The only thing is that everything looks a little boring. And we don’t want to be boring, do we? So let’s add some colors. I decided to set the background color to blue and add white cell borders. Finally, let’s turn the worksheet grid off. Our chart starts to slowly take a final shape.

Unusual chart in Excel

Now, what we need to do is to set conditions for Conditional Formatting, which will turn individual cells on and off, depending on the helper cell (where we keep the amount of cells which need to be coloured).

Let’s select all 100 cells of our chart, go to the Conditional Formatting dialog box and create a new rule.  My first approach to creating a proper rule is:

=OR(ROW($B$11)-ROW()<=INT($M$3/10)-1,AND(ROW($B$11)-ROW()=INT($M$3/10),COLUMN()-COLUMN($B$11)<($M$3/10-INT($M$3/10))*10))

Where $B$11 is the bottom left cell (corner) of our chart and in $M$3 we keep the percentage (number of squares we want to highlight).  You will probably have to adjust it slightly to meet your own needs.

Unusual Excel chart

I decided that I want all the cells fulfilling my condition to be green.

After a few minutes I realized though, that it could be done with a simpler formula (formatting condition):

=((ROW($B$11)-ROW())*10+COLUMN()-COLUMN($B$11)+1)<=$M$3

As before, $B$11 is the bottom left cell (corner) of our chart and $M$3 is the cell with the number of squares we want to highlight.

Until now, our starting point is the bottom left corner of the chart. But what if we wanted to start from the bottom right corner? Or, even better, what if we would like to have a choice?

Unusual Excel chart

All we need to do is to add a list with our choices (to do so I used the Excel validation feature), and to change our formula slightly, which will now look like this:

=((IF($A$5,ROW($B$11),ROW($K$11))-ROW())*10+ABS(IF($A$5,0,COLUMN($K$11))-COLUMN())-IF($A$5,COLUMN($B$11),0)+1)<=$M$3

The cell A5 contains TRUE if “Left2Right” direction is chosen, and FALSE otherwise.

If you have any doubts please download the example file attached and play around with my formulas.

Marcin