changing rows automatically

[mwvirk]

hello friends.

i am working on a huge excel sheet and need your help as i want to finish is ASAP and i want to save my time while working on it.
my question is:
>i have around 500 rows in range of cell a1 to a500
>each cell (a1-a500) contains some machines numbers
>each cell(a1-a500) is also having comments where details of that machine in provided i.e. machine full model/short name/vendor/service provider etc etc
>there are few lines which are important so i want to change colour in those lines. (like machine model, code, location)
> i want line 1 in red colour and bold - line 3 in red colour - line 5 in red

it hard to do it manually for all 500 rows. so creating a keyboard-macro. but its changing all comments in red and bold.

need your help to finish it fast, pls.

thanks

 

[mwvirk]

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+i
'
Range("A151").Comment.Text Text:= _
"ATM/CDM: AA82" & Chr(10) & "Priority Level: X" & Chr(10) & "Branch/Site: Br-O/S - XXXXXXX Branch" & Chr(10) & "Other ATM/CDM: xxxx - xxxx" & Chr(10) & "Serial Number: 99999999" & Chr(10) & "Cassette 1:" & Chr(10) & " Cu"
Range("A151").Comment.Text Text:= _
"rrency: BBB Dinar" & Chr(10) & " Denomination: 10" & Chr(10) & "Cassette 2:" & Chr(10) & " Currency: Bah Dinar" & Chr(10) & " Denomination: 10" & Chr(10) & "Cassette 3:" & Chr(10) & " Currency: Bah Dinar" & Chr(10) & " Denomination: 20" & Chr(10) & "Ca" _
, Start:=200
Range("A151").Comment.Text Text:= _
"ssette 4:" & Chr(10) & " Currency: Bah Dinar" & Chr(10) & " Denomination: 20" & Chr(10) & "IP Address: 99.999.9.99" & Chr(10) & "DSL/GPRS: xxxxxxxx" & Chr(10) & "Speed: XXX kbps" & Chr(10) & "Service Provider: XXX" _
, Start:=400
Range("A151").Comment.Text Text:="X" & Chr(10) & "", Start:=600
Range("A151").Select
End Sub

 

[GK039]

If you want odd rows in red then select your range and in Conditional Formatting type
=MOD(ROW(A1),2)=1

if you want it in even rows then
=MOD(ROW(A1),2)=0

In this example the range starts at A1

George

hello friends.

i am working on a huge excel sheet and need your help as i want to finish is ASAP and i want to save my time while working on it.
my question is:
>i have around 500 rows in range of cell a1 to a500
>each cell (a1-a500) contains some machines numbers
>each cell(a1-a500) is also having comments where details of that machine in provided i.e. machine full model/short name/vendor/service provider etc etc
>there are few lines which are important so i want to change colour in those lines. (like machine model, code, location)
> i want line 1 in red colour and bold - line 3 in red colour - line 5 in red

it hard to do it manually for all 500 rows. so creating a keyboard-macro. but its changing all comments in red and bold.

need your help to finish it fast, pls.

thanks

 

[mwvirk]

thnx george for ur reply.
no i want 1 cell to change. e.g. if i'm in a1 or a2 or a3 (any cell) and 1 hot key micro should change my comments. then i can move cursor to next row and use the same hot key micro to change the comments in that row.
p.s. that comments are all same but values are changing:
machine: xxxxxxx
model: 99999
vendor: aaaaaa
etc

all above are same in each cell but values (xxxxxx or 99999 or aaaaaa) are changing. i want machine in red+bold and vendor in red and so on.

 

[GK039]

Open your vb editor with Alt+F11 and copy-paste

Sub colorit()
Dim j As String

Range("a1").Select
j = InputBox("Type the cell you want to highlight")

Range(j).Select

With Selection
.Font.Bold = True
.Font.ColorIndex = 3
End With

End Sub

thnx george for ur reply.
no i want 1 cell to change. e.g. if i'm in a1 or a2 or a3 (any cell) and 1 hot key micro should change my comments. then i can move cursor to next row and use the same hot key micro to change the comments in that row.
p.s. that comments are all same but values are changing:
machine: xxxxxxx
model: 99999
vendor: aaaaaa
etc

all above are same in each cell but values (xxxxxx or 99999 or aaaaaa) are changing. i want machine in red+bold and vendor in red and so on.

 

[GK039]

Sorry...i forgot...assign it to a button

Open your vb editor with Alt+F11 and copy-paste

Sub colorit()
Dim j As String

Range("a1").Select
j = InputBox("Type the cell you want to highlight")

Range(j).Select

With Selection
.Font.Bold = True
.Font.ColorIndex = 3
End With

End Sub

 

[mwvirk]

that was really nice but i think there is still some confusion... i don;t want to change cell - infact i want to change comments on cells (comments which can open using shift+F2)

Open your vb editor with Alt+F11 and copy-paste

Sub colorit()
Dim j As String

Range("a1").Select
j = InputBox("Type the cell you want to highlight")

Range(j).Select

With Selection
.Font.Bold = True
.Font.ColorIndex = 3
End With

End Sub

 

[mwvirk]

edited: open comments using shift+f2

 

[GK039]

Try this one then

Sub colorit()
Dim cmt As Comment
Dim j As String

Range("a1").Select
j = InputBox("Type the cell you want to highlight")

Range(j).Select

Set cmt = ActiveCell.Comment

With cmt.Shape.TextFrame
.Characters.Font.Bold = True
.Characters.Font.ColorIndex = 3
End With

End Sub

that was really nice but i think there is still some confusion... i don;t want to change cell - infact i want to change comments on cells (comments which can open using shift+F2)

 

[mwvirk]

nope.
its changing the colour for entire comments.
can you do it for me...

i have following fields in my comments:

Branch/Site BR-047 - xxxxxxxxxxx
Region xxxxxx
Site Branch
CDM xxxx
ATM xxxx
IP Phone xxxxxxxx
Timing
Morning 09:00 - 13:00
Evening 17:00 - 20:00
Area Area - 2
Area Manager xxxxxxxxxxx
Branch Manager xxxxxxxxxxx
Direct Phone xxxxxxxxxxx
Mobile Number xxxxxxxxxxx
Fax Number xxxxxxxxxxx
DSL Number xxxxxxxxxxx
DSL Speed xxxxxxxxxxx
DSL Service Provider xxxxxxxxxxx

all above are in normal font+colour (black)
i only want to change first 3 lines (branch/site, region & site) in red and then 3rd last i.e. DSL Number xxxxxxxxxxx in red.
micro shouldn't change anything else and for this i want everything in normal colour (meaning to bold/italic/underline)

thnx again