Highlighting Cells

Sebastian says "I've just found your way to do highlight the active row, it's brilliant! even with Undo function."

He goes on to ask "I'm wondering, how hard is it to make a .xla of this code so I can distribute it around the office, basically I won't have to copy/paste and add conditional format every single time someone wants to use it in a workbook."

Well, yes, the easiest solution is to make an add-in which will add the formatting and code. So I decided to make one. And when I make things, I usually try to add some extra functionality so the add-in I made one to highlight rows, columns or both. Refer to the below pics.

Row Highligting


Column Highligting


Row and Column Highligting


Be warned. This add-in will delete any existing conditional formatting in a range where you use it. It will also insert the VBA code as required, although this should not cause a problem.

Here's the download link if you want to try it.

Rate Calculation

Sorry for misleading you but observe the picture below.



It's actually a game. Mastermind to be exact. But it's disguised so it does not look like a game.

It's something I made over 6 years ago but decided to rewrite the old code to clean it up.

The download link is here. Follow the instructions to see how the game is played.

Now, if you are really interested in Rate Calculation, may I suggest you ask an accountant? Because I'd rather play than hurt my head :-)

VBA Code Indenter Alt

Some people have recently told me of a problem they have with the VBA Code Indenter. It seems the VBE menu items to trigger the indent code fails. (Only a couple of people have told me about this problem so far)

So I have included an alternative add-in to run from the main Excel menu (not the VBE). I've tested it in Excel 2007 and 2010, 32 bit, Windows 7.



Here is the download link.

Copy a Range as HTML

A while back (actually, quite a while back), I promised to write about creating HTML tables in SharePoint using Excel. Sorry to take so long, I've had a few issues since then but hopefully I can blog a bit more now.

As I did not have SharePoint in front of me when I wrote this, please forgive me if I have misnamed some parts of the UI in a Content Editor Web Part, I'm just going from memory.

First, you can create a very simple table in a Content Editor Web Part, just by copying a range and pasting it into the Rich Text Editor. (When I say, "basic", I mean "basic")

Or you can use some VBA code to transform the range into HTML. Here's something I wrote. It will copy basic formatting which you can paste into a HTML editor. The result is spat out into the Immediate Window. If not visible in the Visual Basic Editor (below the Code Window), click the Ctrl + G on your keyboard to bring it up.

Private Sub CopyRangeAsHTML()

    On Error Resume Next

    Dim c As Range
    Dim rw As Long, col As Long
    Dim lCol As Long, lRow As Long
    Dim rwString As String, tblString As String
    Dim aTag As String, bTag As String
    Dim rSpan As Long, cSpan As Long
    Dim cHeight As Double, cWidth As Double
    Dim cAlign As String, cVAlign As String
    Dim bColor As String, fColor As String

    For Each c In Selection

        Exit For

    Next c

    rw = c.Row
    col = c.Column

    For lRow = 1 To Selection.Rows.Count

        rwString = ""

        For lCol = 1 To Selection.Columns.Count

            rSpan = Cells(rw + lRow - 1, col + lCol - 1).MergeArea.Rows.Count

            cSpan = Cells(rw + lRow - 1, col + lCol - 1).MergeArea.Columns.Count

            cWidth = Cells(rw + lRow - 1, col + lCol - 1).MergeArea.Width * 2

            cHeight = Cells(rw + lRow - 1, col + lCol - 1).Height * 2

            Select Case Cells(rw + lRow - 1, col + lCol - 1).HorizontalAlignment

                Case xlLeft: cAlign = "left"

                Case xlCenter: cAlign = "center"

                Case xlRight: cAlign = "right"

                Case xlGeneral

                If IsNumeric(Cells(rw + lRow - 1, col + lCol - 1)) Then cAlign = "right" Else cAlign = "left"

            End Select

            Select Case Cells(rw + lRow - 1, col + lCol - 1).VerticalAlignment

                Case xlTop: cVAlign = "top"

                Case xlCenter: cVAlign = "center"

                Case xlBottom: cVAlign = "bottom"

            End Select

            bColor = Right("000000" & Hex(Cells(rw + lRow - 1, col + lCol - 1).Interior.Color), 6)

            bColor = "#" & Right(bColor, 2) & Mid(bColor, 3, 2) & Left(bColor, 2)

            fColor = Right("000000" & Hex(Cells(rw + lRow - 1, col + lCol - 1).Font.Color), 6)

            fColor = "#" & Right(fColor, 2) & Mid(fColor, 3, 2) & Left(fColor, 2)

            aTag = "<td rowspan=""" & rSpan & """" & " colspan=""" & cSpan & """" & " width=""" & cWidth & """" & " height=""" & cHeight & """" & " align = """ & cAlign & """" & " valign = """ & cVAlign & """" & " bgcolor =""" & bColor & """><font color=""" & fColor & """>"

            bTag = "</font></td>"

            If Cells(rw + lRow - 1, col + lCol - 1).Font.Bold = True Then

                aTag = aTag & "<b>"

                bTag = "</b>" & bTag

            End If

            If Cells(rw + lRow - 1, col + lCol - 1).Font.Italic = True Then

                aTag = aTag & "<i>"

                bTag = "</i>" & bTag

            End If

            If Cells(rw + lRow - 1, col + lCol - 1).Font.Underline <> -4142 Then

                aTag = aTag & "<u>"

                bTag = "</u>" & bTag

            End If

            If Cells(rw + lRow - 1, col + lCol - 1).Font.Strikethrough = True Then

                aTag = aTag & "<strike>"

                bTag = "</strike>" & bTag

            End If

            If Cells(rw + lRow - 1, col + lCol - 1).Address = Cells(rw + lRow - 1, col + lCol - 1).MergeArea.Item(1).Address Then

                rwString = rwString & aTag & Cells(rw + lRow - 1, col + lCol - 1).Text & " " & bTag

            Else

                rwString = rwString

            End If

        Next lCol

        tblString = tblString & "<tr>" & rwString & "</tr>" & Chr(10)

    Next lRow

    tblString = "<table border = ""1"" cellspacing = ""0"" bordercolor=""Black"" style=""border-collapse: collapse"">" & Chr(10) & tblString & "</table>"

    Debug.Print tblString

    On Error GoTo 0

End Sub

This is what the result might look like.

1

2

3

Charts and Excel Services 2

Continuing on from the first post, I learned something very interesting from a colleague doing her own experimenting.

In addition to substituting an Image Web part for an Excel Web Access web part, you could also save an Excel file as a htm file, then after saving it in a suitable location on your SharePoint site, use a Page Viewer web part. It should look great.

I really like this because you can use all kinds of things not possible using a Excel Web Access web part. (Keep in mind any image is not going to be interactive though). For both image (jpg) and htm files, the rendering is a lot better, depending on the complexity of what you are showing.

I'm not done yet. No matter how good things look, somebody may want to print everything. I've noticed that trying to print from Internet Explorer is sometimes not possible because not everything will show (don't know about other browsers as I've not tried them for this)

A solution is to save the charts/ranges from Excel as a PDF file. PDF files can't be used in a Page Viewer web part, but you can upload them as a separate file, then link to them (preferably have some text above or below the Page Viewer web part, along the lines of "Click here to print"). Where I work, the file will open in Adobe which is just the right tool for printing PDF files.

Okay, next time I'll have some pics. Because I want to show you how easy it is for Excel to create HTML tables in SharePoint.