Hi Tom,
I am getting the number that I want now, but it's just telling me where to
find it. I want it to find it and change the fill color to no color.
--
DMM
"Tom Ogilvy" wrote:
> I enriched the macro and tested it - worked fine for me:
>
> Private Sub Commandbutton1_Click()
> Dim ans As Long, rng As Range
>
> ans = Application.InputBox("Enter an integer to format", Type:=1)
> Set rng = Cells.Find(What:=ans, _
> After:=ActiveCell, _
> LookIn:=xlFormulas, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not rng Is Nothing Then
> MsgBox ans & " found at " & rng.Address
> rng.NumberFormat = "#,##0"
> Else
> MsgBox ans & " was not found"
> End If
> End Sub
>
> If the number is produced by a formula, change xlFormulas to xlValues
>
> If the number isn't an integer or if it is produced by a formula, make sure
> you are searching for the exact number - sometimes, a number like 16 might be
> stored or calculated as 15.9999999999999 as an example.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "soconfused" wrote:
>
> > Hi Tom,
> >
> > I am doing this, but it's not finding the number in the worksheet? I'm sure
> > I'm doing something wrong.
> > --
> > DMM
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > put a button from the control toolbox toolbar on the sheet. Double click on
> > > it to take you to the click event (or right click and select view code).
> > >
> > > Put in code like the below pseudo code:
> > >
> > > Private Sub Commandbutton1_Click()
> > > dim ans as Long, rng as Range
> > >
> > > ans = application.Inputbox("Enter an integer to format",type:=1)
> > > set rng = cells.Find(ans)
> > > if not rng is nothing then
> > > rng.NumberFormat = "#,##0"
> > > else
> > > msgbox ans & " was not found"
> > > End if
> > > End Sub
> > >
> > > now go back to excel and take the sheet out of design mode (click on the
> > > depressed button on the control toolbox toolbar with the ruler and drawing
> > > triangle - or their may be a separate floating toolbar with this same
> > > button).
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "soconfused" wrote:
> > >
> > > > I have a worksheet that has numerous columns and rows. I would like to write
> > > > a macro looking for a number then format the cell. I would like to set up a
> > > > button to do this and ask for a different number each time.
> > > > --
> > > > DMM
|
Similar Threads
