Replies

The one time that using the Forms toolbar for this is when you exchange files with the Mac versions of Excel. MS did not include ActiveX support on the Mac versions.

I think it comes from J. Walkenbach.

I know I use it frequently.

For middle name put (checks to see whether there is a middle name):

=IF(ISERR(MID(A1,FIND(” “,A1)+1,IF(ISERR(FIND(” “,A1,FIND(” “,A1)+1)),FIND(” “,A1),FIND(” “,A1,FIND(” “,A1)+1))-FIND(” “,A1)-1)),””,MID(A1,FIND(” “,A1)+1,IF(ISERR(FIND(” “,A1,FIND(” “,A1)+1)),FIND(” “,A1),FIND(” “,A1,FIND(” “,A1)+1))-FIND(” “,A1)-1))

I think, if you have already formatted one worksheet, you can select that area, and click on the Format Painter, then select all worksheets as Hans suggested, and click once where the upper left corner begins it will provide the conditional formatting.

And as an occasional visitor, it would be better to title the thread something like “Conditional Formatting” rather than “Please see the Attachment” (which is not very helpful when browsing).

Thanks.

And as an occasional visitor, it would be better to title the thread something like “Conditional Formatting” rather than “Please see the Attachment” (which is not very helpful when browsing).

Thanks.

Thanks, Hans.

My view on merged cells is that they should be avoided whenever possible. My alternative is to use “Center Across Selection” which effectively does the same, without the negatives of a merged cell (for further use of Excel capabilities).

Thanks, Hans.

My view on merged cells is that they should be avoided whenever possible. My alternative is to use “Center Across Selection” which effectively does the same, without the negatives of a merged cell (for further use of Excel capabilities).

For filling all of column H (as far as Column G extends)

Range(“H1”).AutoFill Destination:=Range(“H1:H” & Range(“G65536”).End(xlUp).Row)

For filling all of column H (as far as Column G extends)

Range(“H1”).AutoFill Destination:=Range(“H1:H” & Range(“G65536”).End(xlUp).Row)

I will try to locate the source of the code, also. [EDIT: I included the links to Jon Peltier’s web site). I modified it to use XL2002 rather than XL97.]

Since I use a Mac at home, I tried these codes in Excel 2001 (Mac OS 9.2.2), and had to slightly modify them:

I had to change one line in each.

Set PPApp = GetObject(, “Powerpoint.Application”)

(Remove the reference to Office Number)

Here is the corresponding one for charts:
(Source: at the bottom of the page http://peltiertech.com/Excel/XL_PPT.html%5B/url%5D )

Sub ChartToPresentation()
' Set a VBE reference to Microsoft PowerPoint 10.0 Object Library for Office 2002,

Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

' Make sure a chart is selected
If ActiveChart Is Nothing Then
    MsgBox "Please select a chart and try again.", vbExclamation, _
        "No Chart Selected"
Else
    ' Reference existing instance of PowerPoint 2002
    Set PPApp = GetObject(, "Powerpoint.Application.10")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    ' Reference active slide
    Set PPSlide = PPPres.Slides _
        (PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
    
    ' Copy chart as a picture
    ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, _
        Format:=xlPicture

    ' Paste chart
    PPSlide.Shapes.Paste.Select
    
    ' Align pasted chart
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
End If

I picked up this handy code, and attached to a toolbar because I use it so often.
(Source: at the bottom of the page http://peltiertech.com/Excel/XL_PPT.html%5B/url%5D )

Sub RangeToPresentation()
' Set a VBE reference to Microsoft PowerPoint 10.0 Object Library for Office 2002,

Dim PPApp As PowerPoint.Application

Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
    MsgBox "Please select a worksheet range and try again.", vbExclamation, _
        "No Range Selected"
Else
    ' Reference existing instance of PowerPoint 2002
    Set PPApp = GetObject(, "Powerpoint.Application.10")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    ' Reference active slide
    Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
    
    ' Copy the range as a piicture
    Selection.CopyPicture Appearance:=xlScreen, _
        Format:=xlPicture

    ' Paste the range
    PPSlide.Shapes.Paste.Select
    
    ' Align the pasted range
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
End If

End Sub

I also have one for Chart ot PPT.

I picked up this handy code, and attached to a toolbar because I use it so often.
(Source: at the bottom of the page http://peltiertech.com/Excel/XL_PPT.html%5B/url%5D )

Sub RangeToPresentation()
' Set a VBE reference to Microsoft PowerPoint 10.0 Object Library for Office 2002,

Dim PPApp As PowerPoint.Application

Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
    MsgBox "Please select a worksheet range and try again.", vbExclamation, _
        "No Range Selected"
Else
    ' Reference existing instance of PowerPoint 2002
    Set PPApp = GetObject(, "Powerpoint.Application.10")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    PPApp.ActiveWindow.ViewType = ppViewSlide
    ' Reference active slide
    Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
    
    ' Copy the range as a piicture
    Selection.CopyPicture Appearance:=xlScreen, _
        Format:=xlPicture

    ' Paste the range
    PPSlide.Shapes.Paste.Select
    
    ' Align the pasted range
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

    ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
End If

End Sub

I also have one for Chart ot PPT.

Thanks