September 30, 2005

More On Automatic Highlighting

Earlier this week I was experimenting again with highlighting using the CELL function.

Highlight the Active Cell
The syntax of the CELL function is CELL(info_type, [reference]), the "reference" part meaning a cell such as A1 etc.

When the reference is omitted, the CELL function refers to the active cell. This can be readily used to highlight with some Conditional Formatting and Screen Updating.

First navigate to the Visual Basic Editor (push Alt + F11), locate your file, then select Microsoft Excel Objects, ThisWorkbook. At the top, you will see 2 dropdown lists. Use the one on the left to select Workbook, then the one on the right to choose SheetSelectionChange. Add "Application.ScreenUpdating = True" like this.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.ScreenUpdating = True
End Sub

Go back to Excel, select your highlight range, then from the top menu choose Format, Conditional Formatting, select Formula Is from the dropdown list and enter this formula.

=CELL("address")=ADDRESS(ROW(),COLUMN())

Push the Format button, make a color selection from the Patterns tab, then push OK twice.

You should get something like this.



The address of the active cell (using CELL) equals that of the address obtained with the ROW and COLUMN functions. TRUE!

Note: You can use Worksheet_SelectionChange instead if you want to work with just one particular sheet, enter the code in the appropriate sheet instead of This Workbook.

Highlight the Row and Column
Last time I mentioned highlighting both the row and column like this.

=OR(CELL("row")=ROW(), CELL("col")=COLUMN())

This results in a cross shape where the row and column overlap. I didn't feel this was the best as a visual aid (the whole purpose of the exercise) so after some experimentation, I came to the conclusion that a reverse "L" was the best (same as seen on Ivan Moala's site, The Xcel Files), in that it seems a lot easier to work with.

Here is the appropriate conditional format (use the same steps as above)

=OR(AND(CELL("row")=ROW(),CELL("col")+1>COLUMN()),AND(CELL("col")=COLUMN(),CELL("row")+1>ROW()))



While I mention it, Ivan told me of his Hex File Reader that contains an example of the code he uses. You can study his code and gain a valuable download at the same time, far too good an opprtunity to miss ;-)  

Posted by andrewe at 02:17

September 25, 2005

Matching More Than One Cell

There are several functions in Excel for referencing cells to get values. Here is one way to reference values in multiple cells.

In the picture below, 2005 has been entered in Cell D2. I've entered this formula in cell E2 to get the corresponding value of "K".

=INDEX($C$2:$C$18,MATCH($D$2,$B$2:$B$18))



What I want now is all other values in Column C until the next value in Column B (2006) so in cell E3 I enter this formula and drag down to E18.

=IF(AND(INDIRECT("B"&MATCH($D$2,$B$2:$B$18)+ROW()-1)="",OFFSET(E3,-1,0)<>""),INDIRECT("C"&MATCH($D$2,$B$2:$B$18)+ROW()-1),"")



What is happening here is that I'm looking for blank cells under 2005 in column B. If these cells are blank and the cell above in Column E is not blank, the true part of the IF formula will show the corresponding cell values in Column C. When 2006 is encountered, the false part the IF formula will cause a blank will show. This results in cells below in Column E to also show as blank as a result of the OFFSET function.

One problem is that I'm left with zeroes for cells that are beyond Row 18. I could go to Tools, Options, View and uncheck Zero values, but I decided to use a custom format instead (Format, Cells, Number, Custom).

[Black][<>0]General

Note: I did make a User Defined Function using the MergeArea property to count merged rows (adaptable for columns), but as demonstrated above regular Excel functions can be used, and they also work with both merged or unmerged cells.  
Posted by andrewe at 19:38

September 22, 2005

Timelines in Excel

Jon Wittwer from the The Excel Nexus contacted me the other day regarding techniques he developed to make timelines using Excel graphs.

This is a picture of a method used to create them very quickly. I had a look at the download file, it was quite simple and easy to use.



Here is a link to the download page. There is also some other material he wrote here. Definitely worth a look!  
Posted by andrewe at 21:49

September 06, 2005

JMT Consulting

Colo and I have been talking about this for some time.



Colo has already been doing consulting work for quite a while now but it's a new experience for me.

And experience is what I am after - most of the time that I use Excel is at my job, and as the company is quite small, there is only so much that I can really learn from working there.

So this is a good opportunity for me to see what people at different companies are doing and how it can be solved.

I'm hoping that learning new things will help me to write and make better stuff, so from the both of us - we would really appreciate your support. Quoting is free, final work is approved by Colo so you can rest assured that you will be getting top level help.

Just click the image link above and you will be taken to our site.  
Posted by andrewe at 20:09

September 03, 2005

Rocket

I've been a little bored recently but I felt much better to hear my Battleships game got included in an Excel games book printed here in Japan.

Being in such a good mood, I decided to make a new game called Rocket (so imaginative with these names huh?).



It's a very simple game and kind of reminds me of the old style games back in the Seventies. (Them were the good ol' days) At least it was good study for me to make other games of this type sometime in the future.

The download link is here.

While I am it, I should mention that Colo's game, Excel Snake was also included in the same book. His Excel Junk Room also has a good selection of Excel games and other free downloads listed on this links page (games are listed at the bottom), it's well worth a visit to see what goodies you can pick up :-)  
Posted by andrewe at 23:38