Date formatting based on criteria

[Guest]

Hi al
I think I need a Macro connected to buttons for this function - it seems too complicated for a formula. I'm a novice at Macro's so anyone could suggest when I write I would greatly appreciate it

Here is what I want to do on an excel spreadsheet

- Find all cells in column A on !Sheet1 that have "Magazines" enter in them
- For all those cells I want to select the same rows covering columns Q:B
- I then want to format them as dates but only show them as "d

I will also need to re-set a selection (ie "current selection") to a normal number format (no decimals). I'm guessing this would be a separate Macro and button

Any help greatly appreciate
BeSmart

 

[jeff]

Try this for your first part (check the spelling
and capitalization of "Magazines").

Sub findMag()
Dim lastrow, crow As Long
Dim r As Range
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Set r = Range("A1:A" & lastrow)
For Each c In r
If InStr(1, c, "Magazines") Then
crow = c.Row
Range("Q" & crow & ":BP" & crow).Select
Selection.NumberFormat = "d"
End If
Next c
End Sub

jeff

-----Original Message-----
Hi all
I think I need a Macro connected to buttons for this

function - it seems too complicated for a formula. I'm a
novice at Macro's so anyone could suggest when I write I
would greatly appreciate it.

Here is what I want to do on an excel spreadsheet:

- Find all cells in column A on !Sheet1 that

have "Magazines" enter in them.

- For all those cells I want to select the same rows covering columns Q:BP
- I then want to format them as dates but only show them as "d"

I will also need to re-set a selection (ie "current

selection") to a normal number format (no decimals). I'm
guessing this would be a separate Macro and button.

 

[Guest]

Thanks Jef
That worked - but for only one of the rows that "Magazines" appears in column A ???
("Magazines" appears on row 19, 20 and 22 with other words typed before, between and after those rows - the macro only worked on row 22)

I checked the spelling of Magazines and they are all spelt correctly and the same
Any advice? Thanks for your hel
BeSmar

----- jeff wrote: ----

Try this for your first part (check the spellin
and capitalization of "Magazines")

Sub findMag(
Dim lastrow, crow As Lon
Dim r As Rang
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Ro
Set r = Range("A1:A" & lastrow
For Each c In
If InStr(1, c, "Magazines") The
crow = c.Ro
Range("Q" & crow & ":BP" & crow).Selec
Selection.NumberFormat = "d
End I
Next
End Su

jef

-----Original Message----
Hi al
I think I need a Macro connected to buttons for this

function - it seems too complicated for a formula. I'm a
novice at Macro's so anyone could suggest when I write I
would greatly appreciate it

have "Magazines" enter in them
- For all those cells I want to select the same rows covering columns Q:B
- I then want to format them as dates but only show them as "d

selection") to a normal number format (no decimals). I'm
guessing this would be a separate Macro and button

 

[Guest]

Scrub that Jeff - I'm an idiot and your macro worked just fine
Thanks agai
BeSmart (Not in this case..)