Search Before Saving

[Guest]

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]

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.

 

[Guest]

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

 

[Dave Peterson]

What didn't work?

And how didn't it work?

And what do you want it to do? It looked like you were trying to look at the
values in a6:n2000 and change colors.

But maybe I misinterpreted.

 

[Guest]

My apologies. I added into sheet 1. it should be added into ThisWorkbook. It
worked.
It colored the cell. Thanks!

But I need it to color the row from A to N. Can you assit me with this?

 

[Dave Peterson]

How do you determine the color of the cells in A:N?

Do you just look at a single cell -- like the one in column A?

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:A2000") '<--change this for the correct column
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.entirerow.resize(1,14).Interior.ColorIndex = icolor
end if
next mycell
End Sub

This line does the shading:
mycell.entirerow.resize(1,14).Interior.ColorIndex = icolor

mycell is whatever cell is being examined.
the .entirerow brings it back to column A.
The .resize(1,14) says to make it 1 row by 14 columns (A:N)

My apologies. I added into sheet 1. it should be added into ThisWorkbook. It
worked.
It colored the cell. Thanks!

But I need it to color the row from A to N. Can you assit me with this?

 

[Guest]

Excellent! Thanks for your help!

I want column D to be the bases. $15 is green ($15) is orange $100 is yellow
($100) is red.