You could use conditional formatting, but as you need a different color for every last column used, it becomes difficult to exactly match a color to a column.
If there are no empty cells in your rows, before the last used cell (for example if F4 is the last used cell on row 4, then cells C4, D4 and E4 are all used),
then you could try this: In cell B4 enter this formula:
=CHAR(66+COUNTA(C4:Z4))It will place the letter of the last column used, in cell B4. Just drag the formula to extend it to other rows.
This works for columns up to Z. If you intend going into column AA and beyond, then a more complex formula is required:
CHAR(66+COUNTA(C4:Z4)))Note that I have split this formula onto four lines for ease of viewing.
I also have a macro that adds colors to cells in column B. I have not used the additional conditional formatting options available in Excel 2007 a lot, so I may be missing an easy way to do this with CF.
Anyway here is a way to do it with code.
Right-click the name tab of your worksheet, at the bottom of the screen. Select View code and paste this into the Visual Basic code window:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim objIsect As Range
Dim rngCell As Range
Dim dblClr As Double
Dim rngLast As Range
'create a range if the cell changed (Target) falls within C2 to Z1000
'change the range as appropriate
Set objIsect = Intersect(Target, ActiveSheet.Range("C2:Z1000"))
'test if the changed cell was in our range
If Not objIsect Is Nothing Then
'find the last used column in the target row
Set rngLast = ActiveSheet.Range("Z" & Target.Row).End(xlToLeft)
'set the color in cell B based on the column number
Select Case rngLast.Column
dblClr = RGB(250, 200, 80)
dblClr = RGB(250, 200, 60)
dblClr = RGB(250, 200, 40)
dblClr = RGB(250, 200, 20)
dblClr = RGB(250, 180, 100)
dblClr = RGB(250, 160, 100)
dblClr = RGB(250, 140, 100)
dblClr = RGB(250, 120, 100)
'add more columns and colors here as required
dblClr = RGB(255, 255, 255)
'set target row, column B color
ActiveSheet.Range("B" & Target.Row).Interior.Color = dblClr
Note that the range B2 to Z1000 should be changed to match the cells that you are working with and which will trigger the color change.
Add additional 'Cases' to cover the maximum number of columns likely to be used. I limited the number of 'Cases' shown so that this post wasn't too long.