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
503 Views

Similar Articles

[PageSpeed] 30

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:

"X" marks the spot
I am working on a call center checklist and I am trying to get c10 t display "X" when b10 is clicked on, c11 to display "X" when b11 i clicked on, and c12 to display "X" when clicked on. This process wil continue with d10/e10, d11/e11, etc. The sheet sample is included for clarification. Thanks for any help in advance, and a good day to all. Vato Attachment filename: sample.xls Download attachment: http://www.excelforum.com/attachment.php?postid=61512 -- Message posted from http://www.ExcelForum.com Hi try the following worksh...

Delete Emails Older Than X Days
Does anyone know if it is possible to configure Exchange Server 2003 Standard Edition to delete any emails in a mailbox (not just in the Deleted Items folder, but also in any other folder) if it's older than X days. My company would like to implement a policy where we delete all emails older than 90 days, and individuals are responsible for archiving their emails if they want to keep them for longer than that. Thanks, Tom On Fri, 24 Jun 2005 14:45:04 -0700, Equilar Tech <techinfo@equilar.com.(donotspam)> wrote: >Does anyone know if it is possible to configure Exchange Serv...

How can I write in a text in a cell using numbers and the letters.
I'm trying to get some codes in for example 511E09 is one fo them but when I try to enter another code it makes the code a formula and it changes to 5.11E+09 why is that? I tried changing it by formatting cells to text, but that doesn't change it. Waht can I do? Clear the data, change it to text, and then enter it. -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" <Sandy@discussions.microsoft.com> wrote in message news:9C474C50-E47D-43EB-A4F0-B4D21141E39F@microsoft.com... > I'm trying to get some codes in for example 511E09 i...

copy/paste into excel changes numbers into date format
I need to copy a lot of raw data from html format into excel. Most of the data consists of a min/max figure (eg. 31/45) Excel (2003) always converts this number to date format no matter what I format the cells as. I have been unsuccesful in converting back to the same format. Any help or advice is appreciated. Thanks Hi try formating the cell s as 'Text' before pasting the values -- Regards Frank Kabel Frankfurt, Germany Rufus wrote: > I need to copy a lot of raw data from html format into > excel. > > Most of the data consists of a min/max figure (eg. 31/45) &...

Excel crashez every time i open a worksheet with formulas
I would apreciate your help, thank you. I have Windows Vista Businees x86, Microsoft Office Basic 2007. The problem first appeared when i reinstalled everything a few days ago. The steps i took are the folowing: Format c: -> Reinstal Vista(internet cabe unpluged) -> install Office -> start Office - > open worksheet(any worksheet) -> Office crashes I also tried the folowing: Format c: -> Reinstal Vista(internet cabe unpluged) -> install SP1 then SP2 -> install Office -> install updates(windows Update) -> all updates installed-> start Office - > open wo...

Word 2001 for OS X
Simple question that I just can't hunt down the answer to, and I hope someone could just answer pretty easily. . . . Does Mac Word 2001 work on the latest version of Mac OS X (10.3.7 I think)??? And I don't mean in classic mode, I mean does it work like any other program without having to use classic mode. I think so, but I need to know for sure. In article <1104957946.508268.170070@f14g2000cwb.googlegroups.com>, jsteinsapir@gmail.com wrote: > Simple question that I just can't hunt down the answer to, and I hope > someone could just answer pretty easily. . . . Doe...

Office X to 2008
Can I upgrade directly from Office X to Office 2008 or do i have to install 2004 first? > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3285129280_3454273 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit On 2/5/08 9:10 PM, in article ee8c3ec.-1@webcrossing.caR9absDaxw, "peldc@officeformac.com" <peldc@officeformac.com> wrote: > Can I upgrade directly from Office X to Office 2008 or do i have to install > 2004 first? The upgrade will ac...

Maximum number of recipient policies
We are talking to a company that hosts Microsoft Exchange. They currently host well over 1000 mail domains and bumped into a limit with the maximum number of recipient policies that Exchange supports. I couldn't find any document that talks about this limit. They get around the limit by not using recipient policies, instead they have an event sink that somehow solves the problem. Does anyone know anything about this limit? What event does their event sink hook into, and what does it do? I really need some help thanks... Regards Jonny I am not aware of any thing that discusses...

Permission for Active X
Every time I go to a certain web site I get a banner across the top of the page saying something like "This site has been blocked from downloading Active X control" Every time I have to click the banner and give permission. I know the site and would like to give it permanent permission. Can I do that ? FDLeyda On Tue, 11 May 2010 23:44:47 -0400, FDLeyda wrote: > Every time I go to a certain web site I get a banner across the top of the > page saying something like > "This site has been blocked from downloading Active X control" > Every time ...

What is my x-value in a trendlinde based on dates?
I have made a chart showing prices every month. Now I have added a trendlinde and can see the formula. If I want to calculate the exact "trend-price" - what is then my x-value for a given month? Hi, You will find information on the LINEST formula, which will allow you to create formula to resolve your problem. http://people.stfx.ca/bliengme/ExcelTips/Polynomial.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "RSunday" <RSunday@discussions.microsoft.com> wrote in message news:61AF0E52-5EED-4958-868F-78EC7DC4239B@microsoft.com... &...

retrieve mailbox size and lastLogon time from LDAP or WMI
Hi, I have two Exchange cluster setup... 1. Exchange 2003 EVS installed on windows 2003 server 2. Exchange 2000 EVS installed on windows 2000 server My requirement is to get Exchange server mailbox size and lastLogon time information using either LDAP or WMI. As I know for both win2k and 2k3, from LDAP I can connect to Active Directory and get all mail user accounts, there lastLogoff time, lastLonon time but not mailbox size info as this info will not be stored at Active directory database. I can use WMI for win2k3 Exchange server to get all this info but there seems to be no WMI supp...

Cannot Open my Microsoft Office MAC
Everytime i click on the icon, it bounces one time on the bottom and then dissapears. Can someone please please help! Email paris522@aol.com with any answers. On Sep 13, 1:25 pm, kate.lau...@gmail.com wrote: > Everytime i click on the icon, it bounces one time on the bottom and > then dissapears. Can someone please please help! Email > paris...@aol.com with any answers. I too have this problem. I uninstalled it and now the installer disk won't even work. I really need it to work because I have school, so help would be amazing. On 9/13/07 1:25 PM, in article 1189715121.229325...

Inserting Recurring Numbers Excel
How do I insert a number in a form that I created? This number should increase by one (1) everytime I open the form? I am lost! Hi do you save this number somethere? -- Regards Frank Kabel Frankfurt, Germany "Nettie" <Nettie@discussions.microsoft.com> schrieb im Newsbeitrag news:D720202E-0EA8-4551-A623-89D1D0865BB4@microsoft.com... > How do I insert a number in a form that I created? This number should > increase by one (1) everytime I open the form? I am lost! ...

Active X buttons HELP!!!
Greetings. I made a simple activex control and I tryed to use it in a modeless dialog of another application (multi document app). Check boxes, lists and everything seems to work just fine except the buttons. When I push a button, seems to loop or something (high CPU). Anyone can help me? Thanks in advance. John. Hi, I'll try to help. I think I had a similar problem once and what I did was go to Project->Add to Project -> Components and Controls -> Visual C++ Components and select "ActiveX Control containment component". That fixed my problem. Hopefully it wi...

How to alter data on HTML webpage into Excell cells as numbers?
A saved HTML webpage is opened with Excel 2003. The HTML webpage has columns of numerical data which go into Excel cells. If we operate on the Excell cells, the contents of the cells do not behave like numbers. This is so also after formatting the cells as numbers. However on the same spreadsheet the other blank cells are properly reformatted as numbers. Simple... When a cell is formated as text and has numaric data in it, changing the format to number or currency will not work. Even thought the format in the Format cell pop up box appears to be in number or currency format. There are ma...

Pictures show red x when published to the web
When I create my .pub document everything looks great and still looks great when I do a web preview of the document. However, when I create my index.html file and post it to the internet through my web hosting company (godaddy.com) no pictures show up. All of the pictures have Red X's and the links to the different pages do not work either. Can someone please help me with this problem? I called godaddy.com and they said it was a problem with my scripting of the pictures. Please help. When you Publish to the Web in Pub 2003, you will produce not only the index.htm file, but als...

What is the table that saves next invoice number?
What is the table that saves next invoice number? Look into "RM40401" Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great Package For Business Solutions daoudm@greatpbs.com http://www.greatpbs.com http://mohdaoud.blogspot.com/ "Malek" <Malek@discussions.microsoft.com> wrote in message news:3EBED9E0-C024-4C8C-A51C-D7D0BBBF7153@microsoft.com... > What is the table that saves next invoice number? > That depends what invoice number you are talking about. If referring to SOP, the invoice number will be stored according to ...

Numbers are changed in Excel?
I created a spread sheet of some stuff. I entered some serial and account numbers. For some reason that everyone but me probably knows the numbers are changed by Excel by inserting a ., a + sign, etc. hat are not as I typed. What's going on here? Thanks. I would say it's some formatting (either default or pre-existing) in those cells. Try giving all those cells the "Text" format. Then Excel won't add decimal places or signs. I -think- Excel won't let you do math with those values (unless you convert them first), but you probably don't need to add or subtra...

how to add an additional title to the x-axis
I need to have 2 different bits of information on the x-axis - both semester and type of assistantship. For example: In 2002 - 2003 there were Teaching assistantships, Research assistantships, Administration assistantships and then the Total # of assistantships. All that shows up is the year with the tick marks for the different assistantships. I want to labe the tick marks Can someone suggest how I might do this? I have tried the help on Excel with no luck. thanks roberta Roberta: There are more ways to do it, depending on what messagey you want to portray. You can make a stacked co...

PowerPoint 2004 replaces charcters with black squares when printed- PPT v.X does not
After installing Office 2004, I tried to print a PowerPoint technical document (138 pages) that I had originally generated in PowerPoint v.X. Many of the 2004 print pages contained black squares in place of the actual character. I used Helvetica for the text, had embedded graphics generated by Canvas X, and MathType 5.1 for equation generation. Some of the imported graphics which had text also showed the "black death" phemnomenum. Note when I printed the document under PPT v.X everything came out perfectly. I am running a dual 2 GHz G5 Macintosh with 3.5 GB RAM under OS 10.4.3 (Tig...

Converting to 2000 from 1.x
Hi- I'm working on a rather old application that uses a Access 1.x database. Unfortunately, Access 2000 does not support conversion from 1.x. Is there any way (other than digging up a really old copy of Access) that I can convert this database? Thanks ...

Excel: Repeating Row 1 Content at top of all printed pages
I see in the Setup - Sheet's Tab you can select Row and column headings to be printed, but that prints the litteral headings such as A, B, C and 1, 2, 3 I need for have the content that I have named my columns reprinted on the top of each page so that users of my printed spreadsheet don't have to flip back to page 1 see what the titile of a certain column is to understand the data in a certain cell in page 3, etc. of the worksheet in printed format. I'm familiar with the freeze pane issue so you can see the title cells when scrolling the worksheet - that's not my issue -...

Setting time on Outlook Express
Hi & thanks for trying to help. My PC clock is set right but my email messages are going out with the wrong time on them. Does anyone know how to adjust the time in side the program Outlook Express so when I send an email it's going out the right time? Thanks ...

Shared public folder calendar in Exchange: how best to have subscribers request vacation time?
I've set up a SBS 2008 Server for our small business (medical practice with four partner physicians). I'm creating a common calendar for partners' work schedules, using categories such as "on call," "vacation," "business meeting", etc. I'd like the subscribers to the calendar to be able to submit requests for time off, on-call changes, etc., to our office manager so that she needs to massage the requests as little as possible before posting them to the shared calendar. Can anyone suggest a streamlined workflow for this? Most subscrib...

I want to use Page x of y without y including cover and TOC
I want to number the body of a report using Page x of y, but I can't figure out how to exclude front material such as cover and toc from the total number of pages y. On Mon, 7 Dec 2009 16:35:01 -0800, Misdeerose <Misdeerose@discussions.microsoft.com> wrote: >I want to number the body of a report using Page x of y, but I can't figure >out how to exclude front material such as cover and toc from the total number >of pages y. The first step is to separate the front matter from the rest of the document with a section break, as described in these articles: ...