This didn't work. I guess I didn't state it correctly. I want the event or
macro to activate before saving.

"Dave Peterson" wrote:

There is no Target in the function call--and since you haven't declared Target,
the "Option Explicit" will make it fail quickly.

I'm not sure I'd depend on the Activesheet when I'm saving the file. I'd be
more explicit:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim icolor As Long
Dim myCell as range
dim myRng as range

with me.worksheets("sheet9999") '<-- change this
set myrng = .range("a6:n2000")
end with

for each mycell in myrng.cells
iColor = -999
Select Case mycell.value
Case 15
icolor = 4
Case -15
icolor = 46
Case 100
icolor = 6
Case -100
icolor = 3
Case Else
'Whatever
End Select

if icolor < 0 then
'do nothing
else
mycell.Interior.ColorIndex = icolor
end if
next mycell
End Sub

Untested. Uncompiled. Watch out for typos.


J-D wrote:

I want this to search for the criteria before saving the file. Pls, can
anbody show me what is wrong or missing?

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim icolor As Integer

If Not Intersect(Target, Range("A6:n2000")) Is Nothing Then
Select Case Target
Case 15
icolor = 4
Case -15
icolor = 46
Case 100
icolor = 6
Case -100
icolor = 3
Case Else
'Whatever
End Select

For myloop = 1 To 14
ActiveSheet.Cells(Target.Row, myloop).Interior.ColorIndex = icolor
Next
End If

End Sub

--

Dave Peterson