Replies

Lugh and Mark – thank you for your responses. My apologies for the delay in responding. I have been away.

I completely agree with your comment Lugh and have taken that into account. I now have a much simpler table (attached) Now I need to inlcude Mark’s switch expression, but for the new table and some advice on how and where I apply it.
46078-table

Thankyou. Kerry

Good point. That’s the kind of discussion I needed to hear.

Actually there is no relation ship between client priority and stage. What I want to make sure of is that if “On Hold” or “Awaiting Client’ are selected the user is prompted or reminded to change the status to “Inactive” and for “Stage” should prompt or remind the user to change the Status to Closed. All the rest do not matter.

Hi Maud
Thankyou and sorry for the delay in responding. The response is “Yes” this should be highlighted.

Mark, sorry for my ignorance, but how would I sort using an expression?
My fieldname is —

Month([Previous Discharge Date (Month)] & ” 1″)

Thankyou Paul. Sadly it isnt working. Is this not possible with a formula?

43299-22

My apology for the lack of information.
What I am trying to do is to create rows from the region columns. So where SA2_DESC has a multiple Regions each one will become a row. The intention is to transfer to the new format. I have removed the total column as it unnecessary for the purposes of this exercise. I have attached another example that hopefully is more meaningful.

Thank you for your response Wendell. Yes I figured it was not going to be easy. I ended up with a work-around that I found. This is the link for interest. http://www.bytes.com/topic/access/answers/193760-easy-sequential-record-numbers-access-queries

Hello Maud

Thank you so much for your response. This works perfectly.

Hi Zeddy

Sorry but this doesnt work?

Maud I do want to apply it to the whole workbook, however on each worksheet it needs to be columns A to AF.

Hi Zeddy and Maud

You fellows are so darn clever! I have had a play and I think I prefer the method that doesn’t use conditional formatting but I want to apply it to “A1:AF348” but done know how to apply this.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

For Each cell In Target ‘process each cell in changed range
zCol = cell.Column ‘get column number for changed cell

Select Case zCol ‘take action depending on column
Case [j1].Column, [k1].Column ‘change detected in col [J] or [K]
If UCase(cell.Value) = “X” Then ‘test if cell value is “x” or “X”
cell.Interior.Color = rgbPaleGreen ‘change cell colour
Else ‘otherwise..
cell.Interior.Color = xlNone ‘clear cell colour
End If ‘end of test for “x” or “X”

Case Else ‘for all other columns..
‘do nothing
End Select

Next cell ‘process next cell in changed range

End Sub

Hello Maud
Thanks for your response. I am able to place this code in a standard module, but I dont understand how to
“place this in the workbook_Open event routine”

Thank you so much for your response. What you and Maud have given me is a great start to work with. I will play with this over the next few days and get back to you if I have any questions. I am familiar with UserForms and can usually understand VB once written, but am not able to write it from scratch.
Kind Regards Kerry

Thanks for the advice. I used your recommendations which all worked. I did, however find that the rtf formatting options such as bullets were quite limited.

I solved my problem! I created a sub report and inserted it into the report. It worked perfectly in my horizontal report layout.