Setting character colour in a macro

[JC]

I use a spreadsheet to analyse the firewall logs for the month to which is added
100-120 data entries per day. I add the data for the current day and then run a
macro which sorts the data based on IP address.

I read through the spreadsheet looking for multiple entries coming from an IP
address and colour the characters where this occurs red for spam, magenta for
port probes etc and blue for pings.

Once that has been completed I run a second macro, which sorts the data back
into date order, and then save the spreadsheet.

I occurred to me that I could save myself quite a bit of checking time if the
second macro could change the characters to grey in all entries where they have
not already been made red, magenta or blue. The process would check the
current colour and change it to grey if it was black.

Since the characters for all new entries added that day would be black this
would reduce my checking down to around 100-120 each day instead of up to 3,500
or so towards the end of the month.

As the month progresses some of those entries previously made grey may need to
be manually changed to red, magenta or blue if another entry or entries from
that IP address occurs in the new entries added that day.

To complicate matters I have 4,000 rows of helper columns H to N to enable the
initial sort based on IP address. The data added each day only occupies
columns A to G and only characters in these columns should be made grey. So
the range selected should only be those rows which contain data in columns A to
G.

It is too fiddly to do this manually each day but it would be great if a macro
could automate the process.

Is this possible? Any ideas on how to do this?

--

Cheers . . . JC

[HiArt]

Hi JC,

at some point you need to set the font ColorIndex, for example

Range(A1, G1).Font.ColorIndex = 1

Perhaps if you haven't set the data to any other colour in the first Macro?

So you could...

If Activecell.Font.ColorIndex = 1 Then 'Black
Range("A" & Activcecell.Row, "G" & Activecell.Row).Font.ColorIndex = 15
End If

15 = 25% Gray
48 = 40%
16 = 50%
56 = 80%

HTH

Art

[Bob Phillips]

JC,

It is difficult to be precise without the data, what is in A-G, and H-N
exactly, but you could do it all by macro, that is set the various colours
for violations as well as the grey for non-violation. You could even sort on
this later to reduce the amount of time that the macro takes each day.
Furthermore, you could do away with the helper columns as you could build
these dynamically, and then remove them.

What I don't fully get, is why do you only colour multiple entries, and how
you determine the spam etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JC" <jhoppyc@westnet.com.au> wrote in message
news:kqui11httkdsm58u7lvr8hsiicqsajapl5@4ax.com...
> I use a spreadsheet to analyse the firewall logs for the month to which is
added
> 100-120 data entries per day. I add the data for the current day and then
run a
> macro which sorts the data based on IP address.
>
> I read through the spreadsheet looking for multiple entries coming from an
IP
> address and colour the characters where this occurs red for spam, magenta
for
> port probes etc and blue for pings.
>
> Once that has been completed I run a second macro, which sorts the data
back
> into date order, and then save the spreadsheet.
>
> I occurred to me that I could save myself quite a bit of checking time if
the
> second macro could change the characters to grey in all entries where they
have
> not already been made red, magenta or blue. The process would check the
> current colour and change it to grey if it was black.
>
> Since the characters for all new entries added that day would be black
this
> would reduce my checking down to around 100-120 each day instead of up to
3,500
> or so towards the end of the month.
>
> As the month progresses some of those entries previously made grey may
need to
> be manually changed to red, magenta or blue if another entry or entries
from
> that IP address occurs in the new entries added that day.
>
> To complicate matters I have 4,000 rows of helper columns H to N to enable
the
> initial sort based on IP address. The data added each day only occupies
> columns A to G and only characters in these columns should be made grey.
So
> the range selected should only be those rows which contain data in columns
A to
> G.
>
> It is too fiddly to do this manually each day but it would be great if a
macro
> could automate the process.
>
> Is this possible? Any ideas on how to do this?
>
> --
>
> Cheers . . . JC

[JC]

On Mon, 21 Feb 2005 09:33:59 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

> JC,
>
> It is difficult to be precise without the data, what is in A-G, and H-N
> exactly, but you could do it all by macro, that is set the various colours
> for violations as well as the grey for non-violation. You could even sort on
> this later to reduce the amount of time that the macro takes each day.
> Furthermore, you could do away with the helper columns as you could build
> these dynamically, and then remove them.
>
> What I don't fully get, is why do you only colour multiple entries, and how
> you determine the spam etc.

Hi Bob

The columns and sample data are as follows:-

Col A Date & time 2005/01/14 21:24:53.480 -
Col B Action taken UDP packet dropped -
Col C Source IP address, port Source:w.x.y.z, port, WAN -
Col D Destination IP address, port Destination:w.x.y.z, port, WAN -

where w, x, y and z are 1, 2 or 3 digit numbers. There are another 2
columns E and F which are as follows:-
Col E may contain Ping, SSH, or the port number
Col F firewall rule invoked

The data in columns A to F come from the firewall log.

Column G I add details of ISP and IP range when I get multiple entries from the
one IP address or address range.

Col J = IF($A1="","",FIND(":",$C1))
Col K = IF($A1="","",FIND(".",$C1))
Col L = IF($A1="","",FIND(".",$C1,$K1+1))
Col M = IF($A1="","",FIND(".",$C1,$L1+1))
Col N = IF($A1="","",FIND(",",$C1))

From these helper columns H and I are calculated as follows:
Col H =IF($A1="","",1000*MID($C1,$J1+1, $K1-$J1-1) +MID($C1,$K1+1, $L1-$K1-1))
Col I =IF($A1="","",1000*MID($C1,$L1+1, $M1-$L1-1) +MID($C1,$M1+1, $N1-$M1-1))

Columns H, I and A are used in the original sort based on IP address. I have
inserted Columns H to N out to row 4,000 to ensure that they exist for the
current expected maximum number of entries in the month.

The firewall sends me email alert messages if it detects thing like port scans
or similar - these I manually colour magenta.

I check for multiple entries from a single IP address or ISP IP address range
and manually colour the characters blue if Ping is in Col E and red for all
other types. The firewall is configured to drop all packets coming from the
WAN that were not initiated by an action on the LAN. I treat anything that I
have not initiated as hostile.

--

Cheers . . . JC

[Bob Phillips]

JC,

It seems that the colour setting is all done maunally, so it reduces the
automation

This is the sort of thing I had in mind. Get rid of your columns H-N, and
run this macro after adding the data

Sub SortData()
Dim sFormula As String
Dim sFormula1 As String
Dim cLastRow As Long
Dim i As Long

sFormula =
"=IF(A1="""","""",MID(C1,FIND("":"",C1)+1,FIND("","",C1)-1-FIND("":"",C1)))"
sFormula1 = "=SUMPRODUCT(MID(H1,FIND(""~""," & _
"SUBSTITUTE("".""&H1&""."",""."",""~""," & _

"ROW(INDIRECT(""1:""&LEN(H1)-LEN(SUBSTITUTE(H1,""."",""""))+1))))," & _
"FIND(""~"",SUBSTITUTE("".""&H1&""."",""."",""~""," & _

"ROW(INDIRECT(""2:""&LEN(H1)-LEN(SUBSTITUTE(H1,""."",""""))+2))))-" & _
"FIND(""~"",SUBSTITUTE("".""&H1&""."",""."",""~""," & _
"ROW(INDIRECT(""1:""&LEN(H1)-" & _
"LEN(SUBSTITUTE(H1,""."",""""))+1))))-1)*(1000^(LEN(H1)-" &
_
"LEN(SUBSTITUTE(H1,""."",""""))+1-" & _

"ROW(INDIRECT(""1:""&LEN(H1)-LEN(SUBSTITUTE(H1,""."",""""))+1)))))"
cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(1, "H").Formula = sFormula
Cells(1, "I").Formula = sFormula1
Range("H1:I1").AutoFill Range("H1").Resize(cLastRow, 2)
Rows("1:" & cLastRow).Sort Key1:=Range("I1"), _
Order1:=xlAscending, _
Header:=xlNo
End Sub

Then run this macro after you have coloured the data

Sub ColourData()
Dim cLastRow As Long
Dim i As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To cLastRow
If Cells(i, "A").Font.ColorIndex = xlColorIndexAutomatic Then
Cells(i, "A").EntireRow.Font.ColorIndex = 48
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JC" <jhoppyc@westnet.com.au> wrote in message
news:qkgk111vc8gjt13dvq4acq8vuce60uos3h@4ax.com...
> On Mon, 21 Feb 2005 09:33:59 -0000, "Bob Phillips"
> <bob.phillips@notheretiscali.co.uk> wrote:
>
> > JC,
> >
> > It is difficult to be precise without the data, what is in A-G, and H-N
> > exactly, but you could do it all by macro, that is set the various
colours
> > for violations as well as the grey for non-violation. You could even
sort on
> > this later to reduce the amount of time that the macro takes each day.
> > Furthermore, you could do away with the helper columns as you could
build
> > these dynamically, and then remove them.
> >
> > What I don't fully get, is why do you only colour multiple entries, and
how
> > you determine the spam etc.
>
> Hi Bob
>
> The columns and sample data are as follows:-
>
> Col A Date & time 2005/01/14 21:24:53.480 -
> Col B Action taken UDP packet dropped -
> Col C Source IP address, port Source:w.x.y.z, port, WAN -
> Col D Destination IP address, port Destination:w.x.y.z, port, WAN -
>
> where w, x, y and z are 1, 2 or 3 digit numbers. There are another 2
> columns E and F which are as follows:-
> Col E may contain Ping, SSH, or the port number
> Col F firewall rule invoked
>
> The data in columns A to F come from the firewall log.
>
> Column G I add details of ISP and IP range when I get multiple entries
from the
> one IP address or address range.
>
> Col J = IF($A1="","",FIND(":",$C1))
> Col K = IF($A1="","",FIND(".",$C1))
> Col L = IF($A1="","",FIND(".",$C1,$K1+1))
> Col M = IF($A1="","",FIND(".",$C1,$L1+1))
> Col N = IF($A1="","",FIND(",",$C1))
>
> From these helper columns H and I are calculated as follows:
> Col H =IF($A1="","",1000*MID($C1,$J1+1, $K1-$J1-1) +MID($C1,$K1+1,
$L1-$K1-1))
> Col I =IF($A1="","",1000*MID($C1,$L1+1, $M1-$L1-1) +MID($C1,$M1+1,
$N1-$M1-1))
>
> Columns H, I and A are used in the original sort based on IP address. I
have
> inserted Columns H to N out to row 4,000 to ensure that they exist for the
> current expected maximum number of entries in the month.
>
> The firewall sends me email alert messages if it detects thing like port
scans
> or similar - these I manually colour magenta.
>
> I check for multiple entries from a single IP address or ISP IP address
range
> and manually colour the characters blue if Ping is in Col E and red for
all
> other types. The firewall is configured to drop all packets coming from
the
> WAN that were not initiated by an action on the LAN. I treat anything
that I
> have not initiated as hostile.
>
> --
>
> Cheers . . . JC

[JC]

On Mon, 21 Feb 2005 22:37:16 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

> JC,
>
> It seems that the colour setting is all done maunally, so it reduces the
> automation
>
> This is the sort of thing I had in mind. Get rid of your columns H-N, and
> run this macro after adding the data
>
> Sub SortData()
> Dim sFormula As String
> Dim sFormula1 As String
> Dim cLastRow As Long
> Dim i As Long
>
> sFormula =
> "=IF(A1="""","""",MID(C1,FIND("":"",C1)+1,FIND("","",C1)-1-FIND("":"",C1)))"
> sFormula1 = "=SUMPRODUCT(MID(H1,FIND(""~""," & _
> "SUBSTITUTE("".""&H1&""."",""."",""~""," & _
>
> "ROW(INDIRECT(""1:""&LEN(H1)-LEN(SUBSTITUTE(H1,""."",""""))+1))))," & _
> "FIND(""~"",SUBSTITUTE("".""&H1&""."",""."",""~""," & _
>
> "ROW(INDIRECT(""2:""&LEN(H1)-LEN(SUBSTITUTE(H1,""."",""""))+2))))-" & _
> "FIND(""~"",SUBSTITUTE("".""&H1&""."",""."",""~""," & _
> "ROW(INDIRECT(""1:""&LEN(H1)-" & _
> "LEN(SUBSTITUTE(H1,""."",""""))+1))))-1)*(1000^(LEN(H1)-" &
> _
> "LEN(SUBSTITUTE(H1,""."",""""))+1-" & _
>
> "ROW(INDIRECT(""1:""&LEN(H1)-LEN(SUBSTITUTE(H1,""."",""""))+1)))))"
> cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> Cells(1, "H").Formula = sFormula
> Cells(1, "I").Formula = sFormula1
> Range("H1:I1").AutoFill Range("H1").Resize(cLastRow, 2)
> Rows("1:" & cLastRow).Sort Key1:=Range("I1"), _
> Order1:=xlAscending, _
> Header:=xlNo
> End Sub
>
> Then run this macro after you have coloured the data
>
> Sub ColourData()
> Dim cLastRow As Long
> Dim i As Long
>
> cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For i = 1 To cLastRow
> If Cells(i, "A").Font.ColorIndex = xlColorIndexAutomatic Then
> Cells(i, "A").EntireRow.Font.ColorIndex = 48
> End If
> Next i
>
> End Sub

Hi Bob,

Many thanks for your help.

I have left in the helper columns at this stage and have added the ColourData
macro lines into my DateSort macro and this works beautifully. I played around
in a test spreadsheet before committing to the one I use each day and the new
day's data stands out like the proverbial. This should cut the checking time
by a large factor.

I don't understand the logic in the Sub SortData. I will be near a bookstore
tomorrow morning and will look for a book on Excel macros and try to figure out
what you are doing. In the mean time, I will make do with the ColourData macro
code. I have coded in Fortran, Basic and QuickBasic before so it shouldn't be
too steep a learning curve.

Once again, thanks for your help.

--

Cheers . . . JC

[Bob Phillips]

"JC" <jhoppyc@westnet.com.au> wrote in message
news:gkkl115mjiu2o6gs3uo6hfqvj8hqshivcv@4ax.com...
> On Mon, 21 Feb 2005 22:37:16 -0000, "Bob Phillips"
> <bob.phillips@notheretiscali.co.uk> wrote:
>
> > JC,
> >
> > It seems that the colour setting is all done maunally, so it reduces the
> > automation
> >
> > This is the sort of thing I had in mind. Get rid of your columns H-N,
and
> > run this macro after adding the data
> >
snip
>
> Hi Bob,
>
> Many thanks for your help.
>
> I have left in the helper columns at this stage and have added the
ColourData
> macro lines into my DateSort macro and this works beautifully. I played
around
> in a test spreadsheet before committing to the one I use each day and the
new
> day's data stands out like the proverbial.

Aah, the benefit of age, wiseness and experience. Sensible move.


> This should cut the checking time by a large factor.

Which is what it is all about.

>
> I don't understand the logic in the Sub SortData. I will be near a
bookstore
> tomorrow morning and will look for a book on Excel macros and try to
figure out
> what you are doing.

The trick in this one is not really in the macro. All the macro does is
create 2 helper columns where you created 8, to get an IP address that can
be sorted. In your spreadsheet, if say the IP address was 1.2.3.4, you ended
up with 1002 in H, and 3004 in I, and presumably you sort on those 2
columns. I end up with 1002003004 in one column and sort on that. So all the
macro does is insert a formula in H (That isoltaes the IP address from
column C) and in I (which makes a number from the IP address), and copy down
to all rows, then sorts on column I. The macro is very simple, it sets the
formulae and sorts the data.

> In the mean time, I will make do with the ColourData macro code.
> I have coded in Fortran, Basic and QuickBasic before so it shouldn't be
> too steep a learning curve.
>

As I said, I think the code is so simple you should be able to understand it
now. The trick is in the formula that I create, which, with all respect,
unless you are into complex worksheet formulae, you are not going to
understand. Just try on a copy workbook and see if it works, if it does, lie
back and enjoy :-)

I would still like to know the rules for colouring the data, to automate
that. :-)


Bob

[JC]

On Tue, 22 Feb 2005 10:14:00 -0000, "Bob Phillips"
<bob.phillips@notheretiscali.co.uk> wrote:

>
> "JC" <jhoppyc@westnet.com.au> wrote in message
> news:gkkl115mjiu2o6gs3uo6hfqvj8hqshivcv@4ax.com...

> > I don't understand the logic in the Sub SortData. I will be near a bookstore
> > tomorrow morning and will look for a book on Excel macros and try to
> > figure out what you are doing.
>
> The trick in this one is not really in the macro. All the macro does is
> create 2 helper columns where you created 8, to get an IP address that can
> be sorted. In your spreadsheet, if say the IP address was 1.2.3.4, you ended
> up with 1002 in H, and 3004 in I, and presumably you sort on those 2
> columns. I end up with 1002003004 in one column and sort on that. So all the
> macro does is insert a formula in H (That isoltaes the IP address from
> column C) and in I (which makes a number from the IP address), and copy down
> to all rows, then sorts on column I. The macro is very simple, it sets the
> formulae and sorts the data.

I haven't had time to test this code out yet. I've grabbed a couple of books
on Excel VBA coding and will settle down with them for a while to get an
understanding on macros.

> > In the mean time, I will make do with the ColourData macro code.
> > I have coded in Fortran, Basic and QuickBasic before so it shouldn't be
> > too steep a learning curve.
> >
>
> As I said, I think the code is so simple you should be able to understand it
> now. The trick is in the formula that I create, which, with all respect,
> unless you are into complex worksheet formulae, you are not going to
> understand. Just try on a copy workbook and see if it works, if it does, lie
> back and enjoy :-)
>
> I would still like to know the rules for colouring the data, to automate
> that. :-)

Bob

The process is simple and difficult at the same time. The simple part is to
look for multiple entries with the same source IP address. What makes it more
difficult is that the end result I want is really based on ISP address ranges.

ISPs have large address ranges. For example ISP X may have an IP address range
from 1.2.3.4 - 1.2.32.255. A complication is that ISP X may have multiple IP
address ranges such as 1.2.3.4 - 1.2.32.255 and 12.16.0.0 - 12.18.255.255.

It would be easy to program a macro to colour multiple entries from a single IP
address but not so easy to cater for ISP address ranges. It requires knowledge
of the entire range of IP address allocations probably in some form of lookup
table and an updating process to keep it current. I check with APNIC etc when
the number of entries exceeds 3 to get the ISP details including address range.
This I put into column G and may result in me uncovering more entries coming
from that ISP's address range. I have listed the most common offending ISPs in
a workbook but this is only a tiny proportion of the entire list.

I send out emails to the ISP if more than 3 entries occur in the month. If
more entries appear on following days I send out additional emails listing all
entries to date this month to reinforce my original request that the spam etc
being sent be stopped. Detecting these additional entries would be more
difficult if the macro automated the colouring process.

This process results in about 20% of the entries being coloured with the rest
remaining grey. I realise that the system I am using is inefficient and does
not detect all cases of multiple entries coming from an ISPs address range.

I hope this helps you understand the process. If you would like to see a copy
of the current spreadsheet I could email it to you.

--

Cheers . . . JC

[Bob Phillips]

"JC" <jhoppyc@westnet.com.au> wrote in message
news:191o11t0gi1vt5e6dm6r4i2f96r1okis09@4ax.com...
> On Tue, 22 Feb 2005 10:14:00 -0000, "Bob Phillips"
> <bob.phillips@notheretiscali.co.uk> wrote:
>
> The process is simple and difficult at the same time. The simple part is
to
> look for multiple entries with the same source IP address. What makes it
more
> difficult is that the end result I want is really based on ISP address
ranges.
>
> ISPs have large address ranges. For example ISP X may have an IP address
range
> from 1.2.3.4 - 1.2.32.255. A complication is that ISP X may have
multiple IP
> address ranges such as 1.2.3.4 - 1.2.32.255 and 12.16.0.0 - 12.18.255.255.

That partly explains the 2 groups of ISP components, I guess it makes that
checking easier.

> It would be easy to program a macro to colour multiple entries from a
single IP
> address but not so easy to cater for ISP address ranges. It requires
knowledge
> of the entire range of IP address allocations probably in some form of
lookup
> table and an updating process to keep it current. I check with APNIC etc
when
> the number of entries exceeds 3 to get the ISP details including address
range.
> This I put into column G and may result in me uncovering more entries
coming
> from that ISP's address range. I have listed the most common offending
ISPs in
> a workbook but this is only a tiny proportion of the entire list.

But it would be easier would it not to maintain a list on a separate
spreadsheet and write code that checks that database rather than do it
manually. Maintaining that list is a simple typing exercise.

> I send out emails to the ISP if more than 3 entries occur in the month.
If
> more entries appear on following days I send out additional emails listing
all
> entries to date this month to reinforce my original request that the spam
etc
> being sent be stopped. Detecting these additional entries would be more
> difficult if the macro automated the colouring process.
>
> This process results in about 20% of the entries being coloured with the
rest
> remaining grey. I realise that the system I am using is inefficient and
does
> not detect all cases of multiple entries coming from an ISPs address
range.

It seems incredibly tedious to me. I understand it might be necessary, but I
am glad it is not me doing it.

> I hope this helps you understand the process. If you would like to see a
copy
> of the current spreadsheet I could email it to you.

If you think there is further help I could offer, I would be happy to.