In the macro window that appears, you should see a macro called ' Highlight_Row_Groups' - select it and then click the ' Run' buttonġ2. On the ' Developer' tab, select the ' Macros' buttonġ1. Go back to your Excel worksheet, and open the ' Developer' tab (instructions here if it's not currently visible - )ġ0. ' Optional : retain borders (these no longer show through when interior colour is changed) by specifically setting themĩ. Rows(intRow).Interior.Color = lngColors(2 + Colr1) ' one colour or the other
If (Cells(intRow, intCol) Cells(intRow - 1, intCol)) Then Colr1 = Not Colr1 'check for different value in intCol, flip the boolean if it's different ' This section defines the column of data with the change defining the row-group colour change (Column 2 by default)ĭim intCol As Integer: intCol = 2 ' define the column with changing valuesĭim Colr1 As Boolean: Colr1 = True ' Will flip True/False adding 2 gives 1 or 2ĭim lngColors(2 + True To 2 + False) As Long ' Indexes : 1 and 2
Whatever happened to lotus 123 code#
In the centre VBA window, copy and paste the following VBA code (note there's a ' Select All' link at the top of the following code window to help with the copy and paste.) -Ĭode: Select all Sub Highlight_Row_Groups()ĭim intRow As Integer: intRow = 2 ' start at 2, cause there's nothing to compare the first row with In the left-hand panel, double-click the new moduleĨ. In the right-click context-menu, select ' Insert', and then select ' Module'ħ. In the left-hand panel, right-click the VBA Project for the new workbook, which will often be called something like Book1Ħ. Press ALT+F11 to open up the Visual Basic Editor windowĥ.
Whatever happened to lotus 123 driver#
Generate some data so that Sheet1 looks something similar to this - ģ, Note that in the above data-set, we've got groups of ' Sectors' listed in Column 2 - this is a main driver of what we're going to do here, so keep this in mind.Ĥ. Macros are brilliant for this sort of stuff though Malcolm, so have a go at this to see how quick and powerful they can be with these sorts of financial-data lists we often see around here -Ģ. I could certainly get used to it.ĭunno - I just kick it until it does what I want it to, and an Excel expert in my mind is just someone who knows a few extra places to kick! I routinely remove all that formatting, but should I be leaving it in, and moving with the times?Ĭertainly, the green-and-white lines aid readability, and reading IAAG's header data into the column headings isn't difficult. I'm asking partly because I've been importing the data from IAAG's regular IT posts, and that's the mode it gets transferred over in. So, in essence, what I'm asking is: what does good-practice corporate Excel look like these days? I routinely remove all that formatting, but should I be leaving it in, and moving with the times? Certainly, the green-and-white lines aid readability, and reading IAAG's header data into the column headings isn't difficult. Is that now the standard? Is that what happens in the corporate world? So: what does 'best-practice' or at least common-practice Excel actually look like these days?īecause I'm increasingly seeing spreadsheets on the Internet that are in table format - you know: sortable columns, green-and-white bands etc. (A shout-out here to user IAAG, who helped me over one or two stumbling blocks with that.)Īll of which is intended to persuade you all that I'm not a complete Excel numpty. I'm comfortable with macros, and as some here know, my VBA skills (while limited), have enabled me to write and develop some user-defined functions to undertake logarithmic linear least squares regression. In 2009 I took a fairly serious Excel course, and have since taken quite a few more. I remained a Lotus 123 user for many years, once I'd left the corporate world, and probably didn't start moving in earnest to Excel until I had to, because clients were sending me Excel spreadsheets, or occasionally wanting analyses done using Excel spreadsheets. When I was employed by two of the Big Six consulting firms in the 80s and very early 90s, Lotus 123 ruled, although Excel was on the horizon. My spreadsheet usage dates back to the Visicalc era, before Lotus 123.
0 kommentar(er)
