Customizing my Document.

E

EricD

I am creating a troubleshooting database. Treating each issue with its own ID
case number. I have a column where I insert the Problem and then another
column where I insert the solution. I want to setup my document to
recoginize when I start typing anywhere in the row it automatically labels
the first cell with the current date and then inserts the Case ID number as
well. Finally, when that is working I want to assign different fill colors
for different levels of completion for that case. Example, when only the
Problem is labeled the row will highlight yellow, when the Problem and
Solution is labeled the row will turn blue, finally when the Case Closed cell
is labled the fill will disappear. I am using Excel 2002 XP Pro.
 
9

95YJ

You can use the conditional format to change fill colors of a cell based on
other criteria like whether or not another cell has data in it. One the
format pulldown, click conditional formating and it will walk you through.
This is a function in Excel 2003, but I believe it also exists in 2002
 
D

Dave Peterson

I would think that conditional formatting would work nicely for your table.

You may want to post what didn't work.

===
You will need an event macro to do the other stuff, though.

Rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
'a = date
'b = id
'c = problem
'd = solution
'e = closed

Set myRng = Me.Range("C2:E65536")

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, myRng) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
With Target
If Me.Cells(.Row, "B").Value <> "" Then
'do nothing, it's been assigned
Else
With Me.Cells(.Row, "B")
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With
With Me.Cells(.Row, "A")
.Value = .Offset(-1, 0).Value + 1
End With
End If

Select Case Application.CountA(Me.Cells(.Row, "C").Resize(1, 3))
Case Is = 0: .EntireRow.Interior.ColorIndex = xlNone
Case Is = 1: .EntireRow.Interior.ColorIndex = 3
Case Is = 2: .EntireRow.Interior.ColorIndex = 4
Case Is = 3: .EntireRow.Interior.ColorIndex = 5
End Select

End With

errHandler:
Application.EnableEvents = True

End Sub

I put the colors in the code--but I'm not sure what colors you wanted (I used
3,4,5).

And I also used this layout:
'a = date
'b = id
'c = problem
'd = solution
'e = closed
(headers in row 1)

And the id was assigned by adding 1 to the previous row.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top