Sorting Macro question

[kim]

1. I have a hiden worksheet that I would like to sort. So far, I am only
able to get the macro to work if I make the worksheet active in order to
sort. I need to keep the sheet hidden. Is there a way to sort a hidden
worksheet?

2. In my spreadsheet I have certain rows that are colored in background RED.
I would like to use the IF in a cell to say something like "IF A1=background
in RED, A1+2, A1*2". Is it possible?

Thanks
Kim

 

[Myrna Larson]

Re #1, You CAN sort a worksheet that isn't active. Please post the code you
have.

Re #2, see www.cpearson.com for help with identifying background colors. BTW,
if the color is a result of conditional formatting, you can't determine what
it is by VBA code. You would have to see which of the conditional formatting
conditions is met.

 

[kim]

The code I use for sorting.
Worksheets("Sheet1").Range("A1:B7").sort Key1:=Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

It's giving me an error stating that "Sort reference is not valid."

 

[Tom Ogilvy]

With Worksheets("Sheet1")
.Range("A1:B7").sort Key1:=.Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End with

the Range("B1") refers to the active sheet since it isn't qualified, so the
above qualifies it.

 

[Myrna Larson]

The error is because of the way you are specifying the key. You write simpley
Range("B1"). That, but definition, is a range on the active sheet. You can't
sort Sheet1 according to a column on a different sheet. You must qualify the
range, i.e.

Worksheets("Sheet1").Range("A1:B7").sort _
Key1:=Worksheet("Sheet1").Range("B1"), _

Or, to shorten the code

With Worksheets("Sheet1")
.Range("A1:B7").sort Key1:=.Range("B1"), _
Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With

Note the periods before each of the Range key words.

 

[Myrna Larson]

Boy, I think I have a record for typos there:

simpley should be simply
but should be by
Key1:= Worksheet( should be Key1:=Worksheets(

 

[kim]

Thanks everyone for your quick response. It's working now.