Ok, so I have just really started off in my programming crusade...I know
a lot of basic html and am now looking at vba after using basic excel
for many uses - managing personal fund to working out odds of
stocks...etc...so I know quite a bit about excel but I want to expand
my knowledge...

Anyway, what I want to do is select column "I" say and search in that
column for every value = "Positive". I then change the font and colour
and edit all cells in that column which are = "Positive". This
therefore allows me to see all of the stocks which has gone
positive...

what I have so far is

Private Sub CommandButton1_Click()

Range("I:I").Select

*********

With Selection.font
..Name = "Arial Black"
..FontStyle = "Bold"
..Size = 10
..Strikethrough = False
..Superscript = False
..Subscript = False
..OutlineFont = False
..Shadow = False
..Underline = xlUnderlineStyleSingle
..ColorIndex = xlAutomatic
End With
Selection.font.ColorIndex = 4

With Selection.Interior
..ColorIndex = 2
..Pattern = xlSolid


End With



End Sub

I am now stuck on the starred part...I want to look up all cells which
have value = "Positive" in that selection and then apply the
formatting.

I'm sure its something easy like =match(Positive,I:I) then do the
formatting stuff....


--
dezzymei
------------------------------------------------------------------------
dezzymei's Profile: http://www.excelforum.com/member.php...o&userid=25052
View this thread: http://www.excelforum.com/showthread...hreadid=385747

why dont u use conditional formatting?

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


dezzymei wrote :


Ok, so I have just really started off in my programming crusade...I
know a lot of basic html and am now looking at vba after using basic
excel for many uses - managing personal fund to working out odds of
stocks...etc...so I know quite a bit about excel but I want to expand
my knowledge...

Anyway, what I want to do is select column "I" say and search in that
column for every value = "Positive". I then change the font and colour
and edit all cells in that column which are = "Positive". This
therefore allows me to see all of the stocks which has gone
positive...

what I have so far is

Private Sub CommandButton1_Click()

Range("I:I").Select

*********

With Selection.font
.Name = "Arial Black"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = xlAutomatic
End With
Selection.font.ColorIndex = 4

With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid


End With



End Sub

I am now stuck on the starred part...I want to look up all cells which
have value = "Positive" in that selection and then apply the
formatting.

I'm sure its something easy like =match(Positive,I:I) then do the
formatting stuff....

I have a question. Is there a reason you don't just select column I and
apply conditional formatting to it?:

Select column I
FormatConditional Formatting
Cell value is: ="Positive"
Click the [Format] button
Select a background color then Click [OK] twice
Done


Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=385747

thank you!:

--
dezzyme
-----------------------------------------------------------------------
dezzymei's Profile: http://www.excelforum.com/member.php...fo&userid=2505
View this thread: http://www.excelforum.com/showthread.php?threadid=38574

Still thinking Conditional Formatting is the way to go (even with
automation), how about this:


Sub FlagPositive()
With Columns("I:I")
..FormatConditions.Delete
..FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlEqual, _
Formula1:="=""Positive"""
..FormatConditions(1).Interior.ColorIndex = 44 'Orange
background
End With
End Sub


Does that help?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=385747

Or just number format the column to show (-) in red.

--
steveB

Remove "AYN" from email to respond
"dezzymei" wrote in
message ...

Ok, so I have just really started off in my programming crusade...I know
a lot of basic html and am now looking at vba after using basic excel
for many uses - managing personal fund to working out odds of
stocks...etc...so I know quite a bit about excel but I want to expand
my knowledge...

Anyway, what I want to do is select column "I" say and search in that
column for every value = "Positive". I then change the font and colour
and edit all cells in that column which are = "Positive". This
therefore allows me to see all of the stocks which has gone
positive...

what I have so far is

Private Sub CommandButton1_Click()

Range("I:I").Select

*********

With Selection.font
.Name = "Arial Black"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = xlAutomatic
End With
Selection.font.ColorIndex = 4

With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid


End With



End Sub

I am now stuck on the starred part...I want to look up all cells which
have value = "Positive" in that selection and then apply the
formatting.

I'm sure its something easy like =match(Positive,I:I) then do the
formatting stuff....


--
dezzymei
------------------------------------------------------------------------
dezzymei's Profile:
http://www.excelforum.com/member.php...o&userid=25052
View this thread: http://www.excelforum.com/showthread...hreadid=385747

And to further your programming crusade, check out vba's help for the
Find method. The example it gives is very close to what you're after.


Regards,
Vic Eldridge



dezzymei wrote:
Ok, so I have just really started off in my programming crusade...I know
a lot of basic html and am now looking at vba after using basic excel
for many uses - managing personal fund to working out odds of
stocks...etc...so I know quite a bit about excel but I want to expand
my knowledge...

Anyway, what I want to do is select column "I" say and search in that
column for every value = "Positive". I then change the font and colour
and edit all cells in that column which are = "Positive". This
therefore allows me to see all of the stocks which has gone
positive...

what I have so far is

Private Sub CommandButton1_Click()

Range("I:I").Select

*********

With Selection.font
.Name = "Arial Black"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = xlAutomatic
End With
Selection.font.ColorIndex = 4

With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid


End With



End Sub

I am now stuck on the starred part...I want to look up all cells which
have value = "Positive" in that selection and then apply the
formatting.

I'm sure its something easy like =match(Positive,I:I) then do the
formatting stuff....


--
dezzymei
------------------------------------------------------------------------
dezzymei's Profile: http://www.excelforum.com/member.php...o&userid=25052
View this thread: http://www.excelforum.com/showthread...hreadid=385747

And to further your programming crusade, check out vba's help for the
Find method. The example it gives is very close to what you're after.


Regards,
Vic Eldridge



dezzymei wrote:
Ok, so I have just really started off in my programming crusade...I know
a lot of basic html and am now looking at vba after using basic excel
for many uses - managing personal fund to working out odds of
stocks...etc...so I know quite a bit about excel but I want to expand
my knowledge...

Anyway, what I want to do is select column "I" say and search in that
column for every value = "Positive". I then change the font and colour
and edit all cells in that column which are = "Positive". This
therefore allows me to see all of the stocks which has gone
positive...

what I have so far is

Private Sub CommandButton1_Click()

Range("I:I").Select

*********

With Selection.font
.Name = "Arial Black"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleSingle
.ColorIndex = xlAutomatic
End With
Selection.font.ColorIndex = 4

With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid


End With



End Sub

I am now stuck on the starred part...I want to look up all cells which
have value = "Positive" in that selection and then apply the
formatting.

I'm sure its something easy like =match(Positive,I:I) then do the
formatting stuff....


--
dezzymei
------------------------------------------------------------------------
dezzymei's Profile: http://www.excelforum.com/member.php...o&userid=25052
View this thread: http://www.excelforum.com/showthread...hreadid=385747