nooby here:D

[dezzymei]

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....

[Ron Coderre]

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

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


Ron

[dezzymei]

thank you!:D

[Ron Coderre]

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

[vba]
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
[/vba]

Does that help?

Ron

[keepITcool]

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....

[STEVE BELL]

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

--
steveB

Remove "AYN" from email to respond
"dezzymei" <dezzymei.1rvfqg_1120860313.2186@excelforum-nospam.com> wrote in
message news:dezzymei.1rvfqg_1120860313.2186@excelforum-nospam.com...
>
> 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
>

[vic@ultratrace.com.au]

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

[vic@ultratrace.com.au]

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