need some quick excel help

[Listener]

Hey, if anyone on here knows Excel (I'm using Office 2007, but saving files as 2003 so I can share them), could you please let me know if this is possible, and help me to set it up?

I have a spreadsheet where the columns = my stories/books that I've written and rows = publications or publishers where I've submitted stuff. I use the letters A, S, and X to indicate the status of a story. What I want to do is set it up so that if there is an S in a cell, the row header and column header change color to indicate "don't submit Story A anywhere else right now, and don't submit to Publication B right now".

Is that even possible? I don't know Excel quite that well.

Thanks.

[CryptoMe]

This is theoretically possible. You could, for example, write a macro to run through your entire spread sheet and set the header colours if a cell contains an S (you'd have to run the maco manually each time you update the spreadsheet) . You need to know a bit of programming, though, and have the patience to figure it out as you go (lots of Excel VBA help is available on the web). The only drawback is that you may not be able to save your spreadsheet in 2003 and still keep the macro.

Good Luck!

[CryptoMe]

Okay, I couldn't help myself. This is very possible.

In Excel 2007, click on the Developer Tab and then click on the Macros icon (on the left, in the Code section).
A Macro dialog box will open. In the Macro name: field, type in some name for your macro and click the Create button.
A Visual Basic window will open, with a blank Module window inside.
Cut and paste the following text (everything between, but not including, the **** rows) into the Module window

*******
Sub ColourHeader()
' Sets Row and Column Header to Red if they contain a cell with letter "S"
' Note, "S" in spread sheet must be a capital!!

Dim ColumnRange As Range, RowRange As Range, OneCell As Range
Dim Col As Integer, Rw As Integer
Dim MaxCol As Integer, MaxRow As Integer
MaxCol = 50
MaxRow = 50

'Clear all Column and Row Headers
Range(Cells(1, 2), Cells(1, MaxCol)).Interior.ColorIndex = xlNone
Range(Cells(2, 1), Cells(MaxRow, 1)).Interior.ColorIndex = xlNone

'Go through each column
For Col = 2 To MaxCol
Set ColumnRange = Range(Cells(2, Col), Cells(MaxCol, Col))
For Each OneCell In ColumnRange
    If OneCell.Value = "S" Then
        Cells(1, Col).Interior.ColorIndex = 3
        End If
Next OneCell
Next Col

'Go through each row
For Rw = 2 To MaxRow
Set RowRange = Range(Cells(Rw, 2), Cells(Rw, MaxRow))
For Each OneCell In RowRange
    If OneCell.Value = "S" Then
        Cells(Rw, 1).Interior.ColorIndex = 3
        End If
Next OneCell
Next Rw

End Sub
********

Now click Run from the Visual Basic (VB) window top menu bar, and select Run Sub/User Forum (or use the green arrow run icon if you can find it).
This should colour the row and column headers for any cell that has a capital "S" in it.
You can now close the VB window.

You can also set up a short-cut key, so that every time you make changes to the spread sheet, you just need to press your short-cut key to have the colouring update.
To do this, click on the Macro icon and in the Macro dialog box, select your macro and click Options.
In the Macro Options dialog box that opens, set your shortcut  key to whatever you want and click OK.
From now on, you can use your shortcut key to update your colour coding (or run through the Macro dialog, your choice).

Oh, and I just checked. You can save this spreadsheet with the Macro in 2003 format.

Good luck and let me know how it goes....

[Listener]

Worked perfectly. Thanks for your help.

I'm pretty good at JS and familiar with .NET, but have never worked with Excel macros before. Just formulas. It never ceases to amaze me how much code is required to just turn something a different color.

[CryptoMe]

Glad to hear it worked and happy I could help.
Apologies if my instructions were overly simplistic - I always assume no knowledge, since you never know what someone does and doesn't know.

You are right that Excel VB is unwieldy!! But in this case, you weren't just trying to change the colour of something (that would have been easy using conditional formatting). You were trying to change the colour of something based on something else - that's where the trouble comes in

[stePH]

Whoa... and I thought I knew a thing or two about Excel.  But as it turns out, I really don't know "Philip K" 

[eytanz]

While I admire CryptoMe's macroing skills, this is actually something that can be done very easily without a macro.

What you need to do is click on the header cells, and then go to "conditional formatting" (in the home ribbon).  From the menu select "new rule".

In the dialogue box select "Use a formula" (the last option). In the "format values when this formula is true", type in "=FIND("S",CONCATENATE(#range#))>0", replacing #range# with the column of values where you are looking for the S (so if your values are in rows 2-9, you should type in B$2:B$9). Then, click on "format" and select what you want the highlighted cells to look like. You can do the same for the rows, you'll just need to type in the range differently for them (if you're using columns B to F, type $B2:$F2).

And that's it. It'll be totally dynamic, so no need to press a button or refresh manually.

It will give you a warning about "significant loss of functionality" if you save to Excel 2003 format, but as far as I can tell the formatting works fine anyway.

[stePH]

It will give you a warning about "significant loss of functionality" if you save to Excel 2003 format, but as far as I can tell the formatting works fine anyway.

It gives that warning if I save one of my spreadsheets in 2003 format, and I don't do anything particularly advanced.  I think it just does it as a rule.

But I've been using OpenOffice Calc instead lately; haven't even gotten around to reinstalling Office XP after my OS overhaul a couple of weeks ago.

[Listener]

It will give you a warning about "significant loss of functionality" if you save to Excel 2003 format, but as far as I can tell the formatting works fine anyway.

It gives that warning if I save one of my spreadsheets in 2003 format, and I don't do anything particularly advanced.  I think it just does it as a rule.

But I've been using OpenOffice Calc instead lately; haven't even gotten around to reinstalling Office XP after my OS overhaul a couple of weeks ago.

Well I only have Calc at home, so I'm hoping that the macro works there too. If not, oh well; I do most of my submitting on my lunch hour anyway.

[CryptoMe]

In the dialogue box select "Use a formula" (the last option). In the "format values when this formula is true", type in "=FIND("S",CONCATENATE(#range#))>0", replacing #range# with the column of values where you are looking for the S (so if your values are in rows 2-9, you should type in B$2:B$9). Then, click on "format" and select what you want the highlighted cells to look like.

Hey etanz,

I cannot get this to work for me. That's why I went the Macro route, because I couldn't get the Conditional Formatting to work when the dependence was on another cell (it works fine if formatting the cell being checked). Even following your instructions above, I cannot get it to work! Any idea why? Could I have a strange setting checked somewhere? 

This is starting to bug me

[eytanz]

Hmmm... Yeah, the problem is the concatenate function, which doesn't work on ranges. It is really stupid. You can find plenty of code for better ones online, but it's still possible to get the same effect with the ampersand if you're willing to manually type up the range.

The rule for using conditional formatting to work with dependencies is as follows:

When you select a rectange of cells to apply the rule to, the cell that is selected first (i.e. the one you start drawing the rectangle from) is your anchor. When you define the rules, then any address you provide will be shifted relative to the distance between the cell in question and the anchor.

In other words, if you draw your rectangle starting from cell C3, then writing "C3" in the formula means "the value of this cell", "C4" is "the value of the cell immediately below this one", and "B3" means "the value of the cell immediately to the left of this one". If you use $ signs, then the address becomes fixed. So "C$4" will be "cell 4 in this column".

This is sort of finnicky to explain, so I uploaded a simple example to my website. You can find it at http://www-users.york.ac.uk/~ez506/downloads/condformat.xlsx

[CryptoMe]

Thanks for that example eytanz. Yes, I also find referencing ranges in Excel to be a hassle. The range definition doesn't seem to handle variables very well.

BTW, it never occurred to me to address each cell individually. In daily life, I deal with data matrices that are 1000's of rows x 1000's of columns each, and then I have 100's of those. So, I always jump to automation right away....