ExcelBanter - nooby heD

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - nooby heD (https://www.excelbanter.com/excel-programming/334037-nooby-here-d.html)

dezzymei

nooby heD

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

keepITcool

nooby heD

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

Ron Coderre[_20_]

nooby heD

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

dezzymei[_2_]

nooby heD

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

Ron Coderre[_21_]

nooby heD

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

STEVE BELL

nooby heD

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

[email protected]

nooby heD

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

[email protected]

nooby heD

All times are GMT +1. The time now is 06:07 PM.