Macro programming.
Thread Starter
Scooby Regular
Joined: Feb 2002
Posts: 7,951
Likes: 0
From: Lurkin Somewhere
Macro programming.
i want a specific macro programming that i cant get my head around doing.(Not used macro in a long time)
just wonder if anyone out there is any good at programmnig these for Excel?
Cheers
S
just wonder if anyone out there is any good at programmnig these for Excel?
Cheers
S
Thread Starter
Scooby Regular
Joined: Feb 2002
Posts: 7,951
Likes: 0
From: Lurkin Somewhere
Right,
Got ports 1-24. If the cell is yellow ignore it, it's a trunk port.
If the number is highlighted blue, i want a total at the end of the row to indicate that there is x amount of free ports on this switch.
Does that help ? not sure if i need a marco. Is that i know some how the cell properties will come into play.
Si
Got ports 1-24. If the cell is yellow ignore it, it's a trunk port.
If the number is highlighted blue, i want a total at the end of the row to indicate that there is x amount of free ports on this switch.
Does that help ? not sure if i need a marco. Is that i know some how the cell properties will come into play.
Si
Scooby Regular
Joined: Sep 1999
Posts: 4,037
Likes: 0
From: Bedfordshire
Si,
Wouldnt it just be easier to have no number in a cell for a trunk port and a number in one that isnt and just total em up?
Or your penchant for pretty colours like yellow and pink getting the better of you? (LMAO)
Gary
Wouldnt it just be easier to have no number in a cell for a trunk port and a number in one that isnt and just total em up?
Or your penchant for pretty colours like yellow and pink getting the better of you? (LMAO)
Gary
Scooby Regular
Joined: Mar 2003
Posts: 2,654
Likes: 1
Paste this in to a VBA module
Then your formula for your spreadsheet would be :-
This checks range a1:a100 for cells with the BACKGROUND colour of Red (Index 3)
This can be modified to suit your needs
Code:
Public Function CountIfColor(rng As Range, clrindx As Integer) Dim Cell CountIfColor = 0 For Each Cell In rng.Cells If Cell.Interior.ColorIndex = clrindx Then CountIfColor = CountIfColor + 1 End If Next Cell End Function
Code:
=CountIfColor(A1:A100,3)
This can be modified to suit your needs
Scooby Regular
Joined: Aug 2005
Posts: 4,291
Likes: 0
the easiest way to get to the right object code is to record a macro doing a similar thing and then go crib the code from the macro.
eg;
Dim r&, c&
c = 2
For r = 1 To 60
ActiveSheet.Cells(r, c).Select
If Selection.Interior.ColorIndex = 6 Then
ActiveSheet.Cells(r, c + 1) = "it was yellow"
End If
Next
does the job
eg;
Dim r&, c&
c = 2
For r = 1 To 60
ActiveSheet.Cells(r, c).Select
If Selection.Interior.ColorIndex = 6 Then
ActiveSheet.Cells(r, c + 1) = "it was yellow"
End If
Next
does the job
Trending Topics
Thread
Thread Starter
Forum
Replies
Last Post