Repeat statements "x" number of times...

Hi All,

I have the following code at the end of a long macro:

    Columns("D:D").Find(What:="total").Select
    ActiveCell.Offset(0, 4).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
    ActiveCell.NumberFormat = "0.00"
    ActiveCell.Font.Bold = True
    With ActiveCell.Interior
        .ColorIndex = 6
    End With

What I am doing is when the word "total" appears, it will go to the cell 4 
columns to the right, and then do a formula and format the cell.

Question:  How can I get this to repeat itself for each time the word 
"total" appears.  Each different time I run this macro, I may have anywhere 
from 1 to 100 totals in the respective worksheet.  I think that I need to 
either loop, or do a for..next statement, but I don't know how to get the 
syntax right, or which would work better.

Any help would be greatly appreciated...thanks in advance...

Sara
0
6/24/2005 1:21:02 PM
excel.misc 78881 articles. 5 followers. Follow

14 Replies
526 Views

Similar Articles

[PageSpeed] 27

No need to repeat the statements, just need to find all the values first.

HTH,
Bernie
MS Excel MVP

Sub FindValues()
Dim c As Range
Dim d As Range
Dim firstAddress As String

'First, find all the cells
With Cells

   Set c = .Find("Total", LookIn:=xlValues, lookAt:=xlWhole)

   If Not c Is Nothing Then
      Set d = c
      firstAddress = c.Address
   Else:
      MsgBox "Not Found"
      End
   End If

   Set c = .FindNext(c)
   If Not c Is Nothing And c.Address <> firstAddress Then
      Do
         Set d = Union(d, c)
         Set c = .FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstAddress
   End If
End With

' Then do what you want with the offset of
' all the cells that have been found:
With d.Offset(0, 4)
   .FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
    .NumberFormat = "0.00"
    .Font.Bold = True
    With .Interior
        .ColorIndex = 6
    End With
End With

End Sub


"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
message news:5DAB77D2-C864-4CD8-8A12-18DF1C2E0CC6@microsoft.com...
> Hi All,
>
> I have the following code at the end of a long macro:
>
>     Columns("D:D").Find(What:="total").Select
>     ActiveCell.Offset(0, 4).Range("A1").Select
>     ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
>     ActiveCell.NumberFormat = "0.00"
>     ActiveCell.Font.Bold = True
>     With ActiveCell.Interior
>         .ColorIndex = 6
>     End With
>
> What I am doing is when the word "total" appears, it will go to the cell 4
> columns to the right, and then do a formula and format the cell.
>
> Question:  How can I get this to repeat itself for each time the word
> "total" appears.  Each different time I run this macro, I may have
anywhere
> from 1 to 100 totals in the respective worksheet.  I think that I need to
> either loop, or do a for..next statement, but I don't know how to get the
> syntax right, or which would work better.
>
> Any help would be greatly appreciated...thanks in advance...
>
> Sara


0
Bernie
6/24/2005 2:59:28 PM
Bernie,

This isn't working...it is popping up the box and saying not found....

"Bernie Deitrick" wrote:

> No need to repeat the statements, just need to find all the values first.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> Sub FindValues()
> Dim c As Range
> Dim d As Range
> Dim firstAddress As String
> 
> 'First, find all the cells
> With Cells
> 
>    Set c = .Find("Total", LookIn:=xlValues, lookAt:=xlWhole)
> 
>    If Not c Is Nothing Then
>       Set d = c
>       firstAddress = c.Address
>    Else:
>       MsgBox "Not Found"
>       End
>    End If
> 
>    Set c = .FindNext(c)
>    If Not c Is Nothing And c.Address <> firstAddress Then
>       Do
>          Set d = Union(d, c)
>          Set c = .FindNext(c)
>       Loop While Not c Is Nothing And c.Address <> firstAddress
>    End If
> End With
> 
> ' Then do what you want with the offset of
> ' all the cells that have been found:
> With d.Offset(0, 4)
>    .FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
>     .NumberFormat = "0.00"
>     .Font.Bold = True
>     With .Interior
>         .ColorIndex = 6
>     End With
> End With
> 
> End Sub
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> message news:5DAB77D2-C864-4CD8-8A12-18DF1C2E0CC6@microsoft.com...
> > Hi All,
> >
> > I have the following code at the end of a long macro:
> >
> >     Columns("D:D").Find(What:="total").Select
> >     ActiveCell.Offset(0, 4).Range("A1").Select
> >     ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
> >     ActiveCell.NumberFormat = "0.00"
> >     ActiveCell.Font.Bold = True
> >     With ActiveCell.Interior
> >         .ColorIndex = 6
> >     End With
> >
> > What I am doing is when the word "total" appears, it will go to the cell 4
> > columns to the right, and then do a formula and format the cell.
> >
> > Question:  How can I get this to repeat itself for each time the word
> > "total" appears.  Each different time I run this macro, I may have
> anywhere
> > from 1 to 100 totals in the respective worksheet.  I think that I need to
> > either loop, or do a for..next statement, but I don't know how to get the
> > syntax right, or which would work better.
> >
> > Any help would be greatly appreciated...thanks in advance...
> >
> > Sara
> 
> 
> 
0
6/24/2005 6:44:06 PM
Bernie...

I think I figured it out....I changed the .find....xlwhole to 
..find.....xlpart...because the totals have fiscal years in front of 
them...i.e. F05 total...


The only question I have now is I only want it to look in column D for this, 
not the whole document because there might be the word "total in other 
columns....

"Frantic Excel-er" wrote:

> Bernie,
> 
> This isn't working...it is popping up the box and saying not found....
> 
> "Bernie Deitrick" wrote:
> 
> > No need to repeat the statements, just need to find all the values first.
> > 
> > HTH,
> > Bernie
> > MS Excel MVP
> > 
> > Sub FindValues()
> > Dim c As Range
> > Dim d As Range
> > Dim firstAddress As String
> > 
> > 'First, find all the cells
> > With Cells
> > 
> >    Set c = .Find("Total", LookIn:=xlValues, lookAt:=xlWhole)
> > 
> >    If Not c Is Nothing Then
> >       Set d = c
> >       firstAddress = c.Address
> >    Else:
> >       MsgBox "Not Found"
> >       End
> >    End If
> > 
> >    Set c = .FindNext(c)
> >    If Not c Is Nothing And c.Address <> firstAddress Then
> >       Do
> >          Set d = Union(d, c)
> >          Set c = .FindNext(c)
> >       Loop While Not c Is Nothing And c.Address <> firstAddress
> >    End If
> > End With
> > 
> > ' Then do what you want with the offset of
> > ' all the cells that have been found:
> > With d.Offset(0, 4)
> >    .FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
> >     .NumberFormat = "0.00"
> >     .Font.Bold = True
> >     With .Interior
> >         .ColorIndex = 6
> >     End With
> > End With
> > 
> > End Sub
> > 
> > 
> > "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> > message news:5DAB77D2-C864-4CD8-8A12-18DF1C2E0CC6@microsoft.com...
> > > Hi All,
> > >
> > > I have the following code at the end of a long macro:
> > >
> > >     Columns("D:D").Find(What:="total").Select
> > >     ActiveCell.Offset(0, 4).Range("A1").Select
> > >     ActiveCell.FormulaR1C1 = "=Sum(RC[-1]-RC[-2])"
> > >     ActiveCell.NumberFormat = "0.00"
> > >     ActiveCell.Font.Bold = True
> > >     With ActiveCell.Interior
> > >         .ColorIndex = 6
> > >     End With
> > >
> > > What I am doing is when the word "total" appears, it will go to the cell 4
> > > columns to the right, and then do a formula and format the cell.
> > >
> > > Question:  How can I get this to repeat itself for each time the word
> > > "total" appears.  Each different time I run this macro, I may have
> > anywhere
> > > from 1 to 100 totals in the respective worksheet.  I think that I need to
> > > either loop, or do a for..next statement, but I don't know how to get the
> > > syntax right, or which would work better.
> > >
> > > Any help would be greatly appreciated...thanks in advance...
> > >
> > > Sara
> > 
> > 
> > 
0
6/24/2005 6:55:02 PM
Change

With Cells

to

With Rand("D:D")


HTH,
Bernie
MS Excel MVP

> The only question I have now is I only want it to look in column D for
this,
> not the whole document because there might be the word "total in other
> columns....


0
Bernie
6/24/2005 7:12:55 PM
Yikes! How did that happen?

With Rand("D:D")

should have been

With Range("D:D")

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:uyRFoCPeFHA.3048@TK2MSFTNGP12.phx.gbl...
> Change
>
> With Cells
>
> to
>
> With Rand("D:D")
>
>
> HTH,
> Bernie
> MS Excel MVP
>
> > The only question I have now is I only want it to look in column D for
> this,
> > not the whole document because there might be the word "total in other
> > columns....
>
>


0
Bernie
6/24/2005 7:27:37 PM
Hi Bernie,

Thanks so much for all the help....The code works great....I can't stop 
re-running it just to see it work.....This is very helpful....and also, 
thanks for the tip on finding the word first, and then executing the 
formating steps.....I would have never even known you can do that.....

One more question........what would be a good direction to go in to learn 
more about visual basic?????  

"Bernie Deitrick" wrote:

> Yikes! How did that happen?
> 
> With Rand("D:D")
> 
> should have been
> 
> With Range("D:D")
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:uyRFoCPeFHA.3048@TK2MSFTNGP12.phx.gbl...
> > Change
> >
> > With Cells
> >
> > to
> >
> > With Rand("D:D")
> >
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > > The only question I have now is I only want it to look in column D for
> > this,
> > > not the whole document because there might be the word "total in other
> > > columns....
> >
> >
> 
> 
> 
0
6/27/2005 2:36:25 PM
Lurk here and read the posts and answers, and read any book in the VBA Power
Programming series by John Walkenbach.

HTH,
Bernie
MS Excel MVP


"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> Hi Bernie,
>
> Thanks so much for all the help....The code works great....I can't stop
> re-running it just to see it work.....This is very helpful....and also,
> thanks for the tip on finding the word first, and then executing the
> formating steps.....I would have never even known you can do that.....
>
> One more question........what would be a good direction to go in to learn
> more about visual basic?????


0
Bernie
6/27/2005 2:50:06 PM
Bernie,

I have some more formatting issues now that pertain to the code you wrote me 
in this thread.

I now need to find in column H...all the cells that have a formula response 
of #N/A, or "India Only", and I need to highlight those in red.  I tried to 
copy this code that formatted the other column for me, but it doesn't work - 
I get a duplicate compile error.  Any suggestions on how I can get these 
responses highlighted????

Thanks.

Sara

"Bernie Deitrick" wrote:

> Lurk here and read the posts and answers, and read any book in the VBA Power
> Programming series by John Walkenbach.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> > Hi Bernie,
> >
> > Thanks so much for all the help....The code works great....I can't stop
> > re-running it just to see it work.....This is very helpful....and also,
> > thanks for the tip on finding the word first, and then executing the
> > formating steps.....I would have never even known you can do that.....
> >
> > One more question........what would be a good direction to go in to learn
> > more about visual basic?????
> 
> 
> 
0
6/29/2005 5:08:05 PM
Don't use code, use conditional formatting.  Look in help.

HTH,
Bernie
MS Excel MVP


"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in message 
news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
> Bernie,
>
> I have some more formatting issues now that pertain to the code you wrote me
> in this thread.
>
> I now need to find in column H...all the cells that have a formula response
> of #N/A, or "India Only", and I need to highlight those in red.  I tried to
> copy this code that formatted the other column for me, but it doesn't work -
> I get a duplicate compile error.  Any suggestions on how I can get these
> responses highlighted????
>
> Thanks.
>
> Sara
>
> "Bernie Deitrick" wrote:
>
>> Lurk here and read the posts and answers, and read any book in the VBA Power
>> Programming series by John Walkenbach.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
>> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
>> > Hi Bernie,
>> >
>> > Thanks so much for all the help....The code works great....I can't stop
>> > re-running it just to see it work.....This is very helpful....and also,
>> > thanks for the tip on finding the word first, and then executing the
>> > formating steps.....I would have never even known you can do that.....
>> >
>> > One more question........what would be a good direction to go in to learn
>> > more about visual basic?????
>>
>>
>> 


0
Bernie
6/29/2005 5:20:27 PM
I tried to use conditional formatting, but it doesn't seem to know that I 
want it to look at a formula result....Also, this is the last bit of 
formatting that I need to do in order to finish a massive macro that I wrote. 
 I would like for it to be included in the macro.  I have recorded a macro 
while doing conditional format, and it looks like this:
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=$O$146"
    Selection.FormatConditions(1).Interior.ColorIndex = 3

the formulas in column H are vlookups with code that looks like this:
    With ActiveCell
        RowCount = .Offset(0, -1).End(xlDown).Row - .Offset(0, -1).Row + 1
        .FormulaR1C1 = _
        
"=IF(RC[-4]=""C"",VLOOKUP(RC[-3],USB,2,FALSE),VLOOKUP(RC[-3],FAS,2,FALSE))"
        .AutoFill .Resize(RowCount)
    End With

Not sure how to combine the two.



"Bernie Deitrick" wrote:

> Don't use code, use conditional formatting.  Look in help.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in message 
> news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
> > Bernie,
> >
> > I have some more formatting issues now that pertain to the code you wrote me
> > in this thread.
> >
> > I now need to find in column H...all the cells that have a formula response
> > of #N/A, or "India Only", and I need to highlight those in red.  I tried to
> > copy this code that formatted the other column for me, but it doesn't work -
> > I get a duplicate compile error.  Any suggestions on how I can get these
> > responses highlighted????
> >
> > Thanks.
> >
> > Sara
> >
> > "Bernie Deitrick" wrote:
> >
> >> Lurk here and read the posts and answers, and read any book in the VBA Power
> >> Programming series by John Walkenbach.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> >> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> >> > Hi Bernie,
> >> >
> >> > Thanks so much for all the help....The code works great....I can't stop
> >> > re-running it just to see it work.....This is very helpful....and also,
> >> > thanks for the tip on finding the word first, and then executing the
> >> > formating steps.....I would have never even known you can do that.....
> >> >
> >> > One more question........what would be a good direction to go in to learn
> >> > more about visual basic?????
> >>
> >>
> >> 
> 
> 
> 
0
6/29/2005 5:36:04 PM
Okay.....I got it to format the "India Only" because that is an actual return 
from my vlookup...The "#N/A" I am having trouble with because that is 
referring to the account number not being on my vlookup table.  I have tried 
that several ways, and can't seem to figure that one out....

here is the code I have for the formatting of India only 
    Range("H:H").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""India Only"""
    Selection.FormatConditions(1).Interior.ColorIndex = 7


"Bernie Deitrick" wrote:

> Don't use code, use conditional formatting.  Look in help.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in message 
> news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
> > Bernie,
> >
> > I have some more formatting issues now that pertain to the code you wrote me
> > in this thread.
> >
> > I now need to find in column H...all the cells that have a formula response
> > of #N/A, or "India Only", and I need to highlight those in red.  I tried to
> > copy this code that formatted the other column for me, but it doesn't work -
> > I get a duplicate compile error.  Any suggestions on how I can get these
> > responses highlighted????
> >
> > Thanks.
> >
> > Sara
> >
> > "Bernie Deitrick" wrote:
> >
> >> Lurk here and read the posts and answers, and read any book in the VBA Power
> >> Programming series by John Walkenbach.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> >> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> >> > Hi Bernie,
> >> >
> >> > Thanks so much for all the help....The code works great....I can't stop
> >> > re-running it just to see it work.....This is very helpful....and also,
> >> > thanks for the tip on finding the word first, and then executing the
> >> > formating steps.....I would have never even known you can do that.....
> >> >
> >> > One more question........what would be a good direction to go in to learn
> >> > more about visual basic?????
> >>
> >>
> >> 
> 
> 
> 
0
6/29/2005 6:02:03 PM
Bernie.....help......please......I am really trying hard......I have been at 
it for almost 4 hours........going crazy....

Can't figure out how to conditional format when the formula result is 
#N/A.....

"Bernie Deitrick" wrote:

> Don't use code, use conditional formatting.  Look in help.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in message 
> news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
> > Bernie,
> >
> > I have some more formatting issues now that pertain to the code you wrote me
> > in this thread.
> >
> > I now need to find in column H...all the cells that have a formula response
> > of #N/A, or "India Only", and I need to highlight those in red.  I tried to
> > copy this code that formatted the other column for me, but it doesn't work -
> > I get a duplicate compile error.  Any suggestions on how I can get these
> > responses highlighted????
> >
> > Thanks.
> >
> > Sara
> >
> > "Bernie Deitrick" wrote:
> >
> >> Lurk here and read the posts and answers, and read any book in the VBA Power
> >> Programming series by John Walkenbach.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> >> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> >> > Hi Bernie,
> >> >
> >> > Thanks so much for all the help....The code works great....I can't stop
> >> > re-running it just to see it work.....This is very helpful....and also,
> >> > thanks for the tip on finding the word first, and then executing the
> >> > formating steps.....I would have never even known you can do that.....
> >> >
> >> > One more question........what would be a good direction to go in to learn
> >> > more about visual basic?????
> >>
> >>
> >> 
> 
> 
> 
0
6/29/2005 8:22:04 PM
Sorry, Frantic - I went on a short vacation...


    Range("H:H").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""India Only"""
    Selection.FormatConditions(1).Interior.ColorIndex = 7
    Selection.FormatConditions.Add Type:=xlExpression, 
Formula1:="=ISERROR(H1)"
    Selection.FormatConditions(2).Interior.ColorIndex = 7


HTH,
Bernie
MS Excel MVP


"Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in 
message news:94CDA8A0-E274-4B4F-B3E2-A8E33C2F205E@microsoft.com...
> Bernie.....help......please......I am really trying hard......I have been 
> at
> it for almost 4 hours........going crazy....
>
> Can't figure out how to conditional format when the formula result is
> #N/A.....
>
> "Bernie Deitrick" wrote:
>
>> Don't use code, use conditional formatting.  Look in help.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in 
>> message
>> news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
>> > Bernie,
>> >
>> > I have some more formatting issues now that pertain to the code you 
>> > wrote me
>> > in this thread.
>> >
>> > I now need to find in column H...all the cells that have a formula 
>> > response
>> > of #N/A, or "India Only", and I need to highlight those in red.  I 
>> > tried to
>> > copy this code that formatted the other column for me, but it doesn't 
>> > work -
>> > I get a duplicate compile error.  Any suggestions on how I can get 
>> > these
>> > responses highlighted????
>> >
>> > Thanks.
>> >
>> > Sara
>> >
>> > "Bernie Deitrick" wrote:
>> >
>> >> Lurk here and read the posts and answers, and read any book in the VBA 
>> >> Power
>> >> Programming series by John Walkenbach.
>> >>
>> >> HTH,
>> >> Bernie
>> >> MS Excel MVP
>> >>
>> >>
>> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
>> >> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
>> >> > Hi Bernie,
>> >> >
>> >> > Thanks so much for all the help....The code works great....I can't 
>> >> > stop
>> >> > re-running it just to see it work.....This is very helpful....and 
>> >> > also,
>> >> > thanks for the tip on finding the word first, and then executing the
>> >> > formating steps.....I would have never even known you can do 
>> >> > that.....
>> >> >
>> >> > One more question........what would be a good direction to go in to 
>> >> > learn
>> >> > more about visual basic?????
>> >>
>> >>
>> >>
>>
>>
>> 


0
Bernie
7/1/2005 2:30:17 AM
Thanks Bernie...

I finally figured it out the other day....

I felt so dumb....the whole time, the formatting wasn't working because I 
wasn't in the first cell of the row I was trying to fix....

I do have another question that would be great if you can help with...it is 
a separate thread "Macro - message box if find nothing"....Jim Rech was 
helping, but he decided I should be able to figure it out on my own, and I am 
stuck...If you don't mind reading thru, I would appreciate some input...

Thanks again...

Sara

"Bernie Deitrick" wrote:

> Sorry, Frantic - I went on a short vacation...
> 
> 
>     Range("H:H").Select
>     Selection.FormatConditions.Delete
>     Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
>         Formula1:="=""India Only"""
>     Selection.FormatConditions(1).Interior.ColorIndex = 7
>     Selection.FormatConditions.Add Type:=xlExpression, 
> Formula1:="=ISERROR(H1)"
>     Selection.FormatConditions(2).Interior.ColorIndex = 7
> 
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in 
> message news:94CDA8A0-E274-4B4F-B3E2-A8E33C2F205E@microsoft.com...
> > Bernie.....help......please......I am really trying hard......I have been 
> > at
> > it for almost 4 hours........going crazy....
> >
> > Can't figure out how to conditional format when the formula result is
> > #N/A.....
> >
> > "Bernie Deitrick" wrote:
> >
> >> Don't use code, use conditional formatting.  Look in help.
> >>
> >> HTH,
> >> Bernie
> >> MS Excel MVP
> >>
> >>
> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in 
> >> message
> >> news:0BAAB0E3-1390-4E0A-9EE5-D3CCC126C212@microsoft.com...
> >> > Bernie,
> >> >
> >> > I have some more formatting issues now that pertain to the code you 
> >> > wrote me
> >> > in this thread.
> >> >
> >> > I now need to find in column H...all the cells that have a formula 
> >> > response
> >> > of #N/A, or "India Only", and I need to highlight those in red.  I 
> >> > tried to
> >> > copy this code that formatted the other column for me, but it doesn't 
> >> > work -
> >> > I get a duplicate compile error.  Any suggestions on how I can get 
> >> > these
> >> > responses highlighted????
> >> >
> >> > Thanks.
> >> >
> >> > Sara
> >> >
> >> > "Bernie Deitrick" wrote:
> >> >
> >> >> Lurk here and read the posts and answers, and read any book in the VBA 
> >> >> Power
> >> >> Programming series by John Walkenbach.
> >> >>
> >> >> HTH,
> >> >> Bernie
> >> >> MS Excel MVP
> >> >>
> >> >>
> >> >> "Frantic Excel-er" <FranticExceler@discussions.microsoft.com> wrote in
> >> >> message news:4636F4E7-4373-4CED-971D-DD27702D9148@microsoft.com...
> >> >> > Hi Bernie,
> >> >> >
> >> >> > Thanks so much for all the help....The code works great....I can't 
> >> >> > stop
> >> >> > re-running it just to see it work.....This is very helpful....and 
> >> >> > also,
> >> >> > thanks for the tip on finding the word first, and then executing the
> >> >> > formating steps.....I would have never even known you can do 
> >> >> > that.....
> >> >> >
> >> >> > One more question........what would be a good direction to go in to 
> >> >> > learn
> >> >> > more about visual basic?????
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
7/1/2005 3:19:04 PM
Reply:

Similar Artilces:

plotting multiple x y data on same graph
my friend is pulling his hair out trying to plot something for his engineering class and i figured id ask on the board.. he has a large amount of data, grouped in twos. theres about ten sets of two. each set he wants plotted as x and y respectively, but all on the same line graphy. I think he laid it out wrongly but he w3ont hear any of it and its been a few years since i was using excel myself. so how does one plot it out as thus on the same graph. data looks something like this x y x y x y # # # # # # # # # # # # # # # # # # etc thanks If we a...

Anti Aliasing Quit in Office X
The Quartz anti aliasing has recently ceased to function in MSIE and Office. I installed the patch update for MSIE and that fixed the issue there. However, MSFT Office is not responding to the update to fix the issue. Any ideas as to how I can fix the anti aliasing issue? It is really annoying and makes reading documents difficult.. Thank you. M. Sagi pallor@subnova.com First, go to Apple -> About this Mac, and make sure your OS version is at least 10.1.5. If it is any lower, go to Apple -> Software Update and update to the latest version. From there, go to Apple -> System Prefe...

How do I stop a Textbox in a Userform converting numbers?
Probably a really stupid question... but how do I stop a Textbox on userform from removing the leading zero on a number? eg 01234567890 becomes 123456789 -- Message posted from http://www.ExcelForum.com Alan You must format the cell first as text. e.g Private Sub CommandButton1_Click() Range("A6").NumberFormat = "@" Range("A6").Value = TextBox1.Text End Sub Regards Peter >-----Original Message----- >Probably a really stupid question... but how do I stop a Textbox on a >userform from removing the leading zero on a number? > >eg 01234...

Excel automatically changes the formatting of the cell to "Time"
Hello, I have a long column of numbers [dates in the YY:DD format]. I wanted to replace ":61" to ":59". Even though the cells are initially formatted as Text, as soon as I make the change, Excel changes the formatting to Time, and the cell with the change now has text ":59:00" in it. Is it possible to force Excel to keep the cells formatted as Text? Thank you! Sam, If the cells are truly formatted as text (Format - Cells - Number - Text), the formatting should never change, and you should always see exactly what you've typed. Give us an example...

Active X problem
I have seen this error below on several users with various revs of Outlook. It looks like it occurs when people have fancy Outlook background or signatures. "Your current settings prohibit running Active X controls on this page." How can I fix this problem? I have tried to change security settings on the browser but it doesn't stay, even if logged in as an administrator. Thanks in advance! Please include more information about Outlook/Office Version, O/S, IE version, etc. Have you checked what Outlook security is set for? Outlook 2002 will install by default to use ...

Shorten a 7 digit number in a text field
I have a text field with 7 digit numbers in it (none starting with zero) called 'USE_DCAT'. I want to run a query that shortens the 7 digits to the first 3. I am using: Left ([qryRPI.USE_DCAT], 3) but this returns nothing. Any help would be greatly appreciated. Per DLT: >I have a text field with 7 digit numbers in it (none starting with zero) >called 'USE_DCAT'. I want to run a query that shortens the 7 digits to the >first 3. I am using: Left ([qryRPI.USE_DCAT], 3) but this returns nothing. >Any help would be greatly appreciated. The syntax looks unfamiliar...

Frx for Dynamics Btrieve/Pervasive will need a registration number
Hi, I am currently covering for the normal manager of information systems. They currently have great plains 7.00g12 and Frx6.5. Everything was working for years, until today. Today, when trying to use Frx, all users are getting the following error: Frx for Dynamics Btrieve/Pervasive will need a serial number in 27 days. The thing is after this screen they can't use the program at all anymore, yet on my machine I do get the error, but at least can use it. I do have an administrative account though, while they don't. So, I went looking through all of the managers files found...

When forwarding a message only a red x appears in a blank box?
When I try to forward a message with a picture, the receiver, receives the written part but no pictures are sent only a blank box where the picture would be and a small red x in the top right corner. How can I correct this ...

Excel Chart X axis
I am trying to chart a weekly program on Excel. Is there any way to get my X axis to scale in weeks. The only choices seem days, months, or years. Jack - Unless you need something like week number, you can set the scale to days, and use 7 days as your major unit. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Jack Fluga wrote: > I am trying to chart a weekly program on Excel. Is there > any way to get my X axis to scale in weeks. The only > choices seem days, months, or years. ...

Simple Macro to copy each row in the database "X" times? (Excel 2002)
This is a multi-part message in MIME format. ------=_NextPart_000_000D_01C3ECA5.0E42E590 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I was wondering if someone could help me with a macro to cut down on = some repetitive task activity. I have a regularly increasing DB of address information in Excel with = several hundred entries with row 1 as the header row. Depending on the = demand, we have to create mail labels in various quantities per address, = consecutively. I foresee a macro that when kicked off will prompt the = user to ente...

Windows Installer loads every time I open Excel 2007
Hi all, I'm facing the following problem: when I open Excel 2007 the windows installer loads every time... is not going to give me any error, but it's something annoying... does anyone has any solution??? thanks Vit p.s. this happens also with other office 2007 applications Try clicking "Help" and then "Detect and Repair..." That worked for me when I had the same problem. ...

Maximum Number of Cell Formats
My company uses very large spreadsheets to confrom to goverment requirements. Recently we have run into a Warning message when we try to insert a new column. It says we've used up all the cell formats, so we can't add any new cells. The Microsoft documentation seems to indicate that the maximum number of cell formats is 4000. I guess we're hitting that limit and Excel won't let us enter anymore data. How do I increase the max number of cell formats? How do I find out how many I've used already? How do I delete ones I'm not using (Excel seems to "hang on&quo...

Extract numbers
Hi, I have a field in my database where it's written "1d" or "12d" ecc.. I want to use an equation in a query to display the number included in this field only,meaning another field where it's written "1" or "12" how can i do this ? Try --- Val([YourField]) -- KARL DEWEY Build a little - Test a little "Pietro" wrote: > Hi, > I have a field in my database where it's written "1d" or "12d" ecc.. > I want to use an equation in a query to display the number included in this > field only,me...

Conversion of Dec numbers to Bin, Oct and Hex and visa versa
I'm attempting to produce an excel spreadsheet that can convert decimal numbers (up to any value) to binary, octal and hexadecimal. Conversely I'm also trying to produce an excel spreadsheet that can convert Hex values to binary, octal and decimal. I need to create this spreadsheet without the use of the dec2hex, dec2bin etc excel functions. Basically the spreadsheet needs to be able to replicate the results of these excel functions without actually using them. The solution has to be contained within the spreadsheets. I can't use the VB module within Excel to derive the solu...

X-axis scale problem
I am attempting to plot two sets of data (x1,Y1) and (x2,y2) on a combined scatter chart. BUT (x1,y1) data is not in the correct orientation I need to present it so I have to reverse the x scale so that it read from right to left . . .4, 3,2,1,0 so zero is at the far right end of the data chart. I got this part, but I need to put the second set of data (x2,y2) on this very same chart but it needs to start from the modified x axis on zero and count up i.e 0, 1, 2, 3, ect. so my x axis should look like this when finished 3, 2, 1, 0, 1, 2, 3 etc... I think my problem is that you can only plot one...

get the x and y coordinates of the caret position inside IE
Hi I am writng an application in mfc which have to monitor the internet explorer and to get the x and y coordinates of the caret position. I already knows how to get IHTMLDocument2 from the HWND but was not able to figure out how to get the position of the caret inside the document. i understood that the IHTMLCaret cannot be retrieve because my application is in a different process. So what is the solution for this i will be very happey to hear any suggestion because i am stuck with this problem for almost two weeks now. ...

advanced find
Currently (even in v3) there is no way to find all opportunities where Modified On < last X days. our customers are looking for views that would display opportunities/accounts not modified for the last X days and have no activities modified in the last X days. The issue would be solved if there was before last X days(months/years) operator in the advanced find for date fields. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane....

Check Numbers in 2006 Deluxe
Money 2006 is not pulling in the check number when I download transactions from my bank. I know the check numbers are in the file because if I open the file with notepad I see the check numbers. This makes it a little more diffiuclt to reconcile my entries with the banks entries. Any help in resolving this issue is greatly appreciated. Thanks, Jason "Jason" <Jason@discussions.microsoft.com> wrote in message news:BE0E1BF9-50FC-4952-B9A3-03D08FFC22C0@microsoft.com... > Money 2006 is not pulling in the check number when I download transactions > from my bank. I know ...

Counting groups of exact numbers in a huge list (column)
I need to know how I can write a formula to count a very big list of numbers and return the total number of numbers that are different in the list. For example: 8800719 8800718 8800718 8800717 8800719 8800715 8800719 8800715 As you can see there are 8 numbers in this list but of those 8 there are only 4 different case numbers. So if I had a list of several hundred or thousand case numbers like the ones above, and many of them had duplicates somewhere in the list, how can I get excel to give me a total number of all the similar case numbers? If they are all numbers: =SUM(IF(FREQUENCY(A...

Convert text to number automatically
I am trying to compare drawing revisions from 2 different sources but am having difficulty due to the sources having either text or number formats. Sheet 3 (PH1) is the 1st source and is from another excel spreadsheet - no problem. All formats are GENERAL. A B 1 Drwg Rev 2 0001 0 3 0002 0 4 0003 A 5 0004 A Sheet 2 (PH2) is the 2nd source and is an export from an external document management program that I would like to import weekly. I want to use this sheet exclusively for pasting export data only (ie do not do anything else ...

Different Version Numbers for Excel, Word, PPT
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Today, I decided to make sure everything was up to date. I usually let AutoUpdater do its thing so I was surprised to find that Word was currently version 11.3.8. I downloaded the 11.3.9 update but when I tried to install it, the only versions it found for updating were on my Time Machine Backup drive. After restoring Office a couple of times, I've discovered that I have in fact run all the updates properly (as shown in the Updater Log), but that Excel is currently version 11.4.1, Word is 11.3.8 and PPT is 11.3.5....

Sequential numbering when X column repeats records
I have a table with 2 columns Column 1 has records A A A B B C A Column 2 needs to have the number of times row one repeats a record. Something like this 1 2 3 1 2 1 Any formula that will find records on column one and sequentially number column B depending on amount of records? Thank you -RoMo -- RoMo robpiolink, Give each record a value of one with a calculated bound field in your query... GrpCounter : 1 Add GrpCounter to the report section. Group your report on each letter (in this example), and set GrpCount Running S...

Monitoring Exchange 2003 SMTP Round trip times?
In Exch2000/2003, how do I measure/log/report on SMTP round trip times to various external locations. In Exch5.5 I used link monitors to other exch servers, and various SMTP destinations. SMTP link monitors would send a message to a non-existent address at some external mail system and time the NDR to come back. Exch5.5 link monitors could generate alerts when thresholds were exceeded... How do I do the same in 2000/2003? ...

change custom format number to text
i have a long colum of custom formated dates i need to change them into text in order to prosses them -the custom format is dd/mm/yyyy h:mm the cell reads 01/09/2005 9:30 the number that shows up is 38596.39583 when i try to convert the format to standard text. is there a function that will do this for me? Just add an apostrophy in front of the date. Be sure to do it in the formula bar. So if you see 9/16/2005 make it '9/16/2005 -- Gary''s Student "joey" wrote: > i have a long colum of custom formated dates i need to change them into text > in order to p...

Word X and Word 97
I am sending Word X documents to clients using Word 97 and Word 2000=20 and they are unable to open them. What can I tell them to do in order = to=20 open my documents? Make sure a ".doc" extension is added to the end of the filename of the Word document. Some other compatibility issues are mentioned here: <http://makeashorterlink.com/?M40A25866> This process might work to open the files: Tell the user to open Word on the Windows machine, and then go to File -> Open. Tell him/her to choose to show "All files (*.*)", and then tell him/her to navigate to and op...