Paul, thanks.
Here is the code to highlight the cell:
With Selection.Interior
.ColorIndex = 47
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub
Now, the IsPresent() function would be used underneath a set of
numbers in column B. The whole purpose of this exercise is to: match
numbers in column B with numbers in column C, highlight all matched
numbers in column B, and then count the matched numbers in column B.
The reason why I'd like it as a function is to simply the life of
others who wish to add columns D,E,F,G, etc... that will be used to
match against column C.
I tried your function (=ispresent("B"&ROW()) but it gives me a #NAME?
error.
:)
Cheers.
On Oct 16, 8:03 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> OK.. excel 2003 may be our problem.
> it may be that the "highlighting" syntax is
> not compatible.
>
> Record a macro.
> Select a cell and change the "highlighting" to
> whatever you wish.
> Clear the highlighting.
> stop recording.
>
> now, your macro will show you the proper syntax
> for the highlighting.
>
> Next...
> you "wish this was a code as a function"...
> Then you'll have to tell me what you want the function
> to do.
> This sub resets all of the highlighting.
> then loops through the list in B3:B20
> and checks for each item in C3:C99.
>
> If you want it as a FUNCTION, then
> you need to tell me how you expect to USE it.
>
> I mean, I could write it to be used like:
>
> cell D3: =IsPresent(B3)
>
> then the function would highlight B3 if it is found in C3:C99
> but you would NOT want it to reset all highlighting...
>
> so, you COULD use:
> =ispresent("B"&ROW())
> and the function:
>
> Option Explicit
> Public Function IsPresent(ByVal Addr)
> Dim bRow, cRow
> 'Reset previous "highlights"
> Range(Addr).Font.Bold = False
> Range(Addr).Font..ColorIndex = xlAutomatic
> For cRow = 3 To 99
> If Range(Addr).Value = Cells(cRow, "C")..Value Then
> Range(Addr).Font.Color = vbRed
> Range(Addr).Font.Bold = True
> Exit For
> End If
> Next cRow
> IsPresent = ""
> End Function
>
> Paul
>
> ________________________________
> From: Mark Livingstone <namematters...@msn.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Thursday, October 15, 2009 2:57:13 PM
> Subject: $$Excel-Macros$$ Re: How to match numbers from 2 columns with
> minimal formulas
>
> Excel 2003. I go to add a module, copy&paste your code and then excute
> the macro.
>
> BTW, I wish this was code as a function.
>
> On Oct 15, 1:39 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>
>
> > what version of excel are you using?
> > WHERE are you getting the run-time error?
>
> > Paul
>
> > ________________________________
> > From: Mark Livingstone <namematters...@msn.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Thursday, October 15, 2009 11:18:07 AM
> > Subject: $$Excel-Macros$$ Re: How to match numbers from 2 columns with
> > minimal formulas
>
> > Paul, thank you. Unfortunately this code doesn't work. I get run-time
> > error 438.
>
> > On Oct 15, 9:05 am, Paul Schreiner <schreiner_p...@att.net> wrote:
>
> > > you sure don't give much to go on...
> > > what do you mean by "highlight"?
>
> > > there are lots of ways to do what you're asking.
> > > here's one I threw together.
> > > it "highlights" by changing the font to bold and red:
>
> > > Option Explicit
> > > Sub IsPresent()
> > > Dim bRow, cRow
> > > 'Reset previous "highlights"
> > > Range("B3:B20").Select
> > > Selection.Font.Bold = False
> > > With Selection.Font
> > > .ColorIndex = xlAutomatic
> > > .TintAndShade = 0
> > > End With
> > > Range("A1").Select
> > > 'Loop through range
> > > For bRow = 3 To 20
> > > For cRow = 3 To 99
> > > If Cells(bRow, "B").Value = Cells(cRow, "C").Value Then
> > > Cells(bRow, "B").Font.Color = vbRed
> > > Cells(bRow, "B").Font.Bold = True
> > > Exit For
> > > End If
> > > Next cRow
> > > Next bRow
> > >
> > > End Sub
>
> > > Paul
>
> > > ________________________________
> > > From: Mark Livingstone <namematters...@msn.com>
> > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > Sent: Wednesday, October 14, 2009 12:44:12 PM
> > > Subject: $$Excel-Macros$$ How to match numbers from 2 columns with
> > > minimal formulas
>
> > > Hi.
>
> > > I've been trying to put together VBA code that will allow me to match
> > > 2 columns with minimal Excel formulas.
>
> > > There are 2 columns. 1st column (B3 to B20) has a set of numbers.. 2nd
> > > column (C3 to C99) has another set of numbers. I need to highlight
> > > cells in the 1st column IF the cell's content matches any of the
> > > numbers in the 2nd column.
>
> > > Ideally, I would like to use VBA code only. Could anyone please cook
> > > something up or provide a code that already exists?
>
> > > Thanks!!!- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
On Oct 16, 8:03 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> OK.. excel 2003 may be our problem.
> it may be that the "highlighting" syntax is
> not compatible.
>
> Record a macro.
> Select a cell and change the "highlighting" to
> whatever you wish.
> Clear the highlighting.
> stop recording.
>
> now, your macro will show you the proper syntax
> for the highlighting.
>
> Next...
> you "wish this was a code as a function"...
> Then you'll have to tell me what you want the function
> to do.
> This sub resets all of the highlighting.
> then loops through the list in B3:B20
> and checks for each item in C3:C99.
>
> If you want it as a FUNCTION, then
> you need to tell me how you expect to USE it.
>
> I mean, I could write it to be used like:
>
> cell D3: =IsPresent(B3)
>
> then the function would highlight B3 if it is found in C3:C99
> but you would NOT want it to reset all highlighting...
>
> so, you COULD use:
> =ispresent("B"&ROW())
> and the function:
>
> Option Explicit
> Public Function IsPresent(ByVal Addr)
> Dim bRow, cRow
> 'Reset previous "highlights"
> Range(Addr).Font.Bold = False
> Range(Addr).Font..ColorIndex = xlAutomatic
> For cRow = 3 To 99
> If Range(Addr).Value = Cells(cRow, "C")..Value Then
> Range(Addr).Font.Color = vbRed
> Range(Addr).Font.Bold = True
> Exit For
> End If
> Next cRow
> IsPresent = ""
> End Function
>
> Paul
>
> ________________________________
> From: Mark Livingstone <namematters...@msn.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Thursday, October 15, 2009 2:57:13 PM
> Subject: $$Excel-Macros$$ Re: How to match numbers from 2 columns with
> minimal formulas
>
> Excel 2003. I go to add a module, copy&paste your code and then excute
> the macro.
>
> BTW, I wish this was code as a function.
>
> On Oct 15, 1:39 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>
>
> > what version of excel are you using?
> > WHERE are you getting the run-time error?
>
> > Paul
>
> > ________________________________
> > From: Mark Livingstone <namematters...@msn.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Thursday, October 15, 2009 11:18:07 AM
> > Subject: $$Excel-Macros$$ Re: How to match numbers from 2 columns with
> > minimal formulas
>
> > Paul, thank you. Unfortunately this code doesn't work. I get run-time
> > error 438.
>
> > On Oct 15, 9:05 am, Paul Schreiner <schreiner_p...@att.net> wrote:
>
> > > you sure don't give much to go on...
> > > what do you mean by "highlight"?
>
> > > there are lots of ways to do what you're asking.
> > > here's one I threw together.
> > > it "highlights" by changing the font to bold and red:
>
> > > Option Explicit
> > > Sub IsPresent()
> > > Dim bRow, cRow
> > > 'Reset previous "highlights"
> > > Range("B3:B20").Select
> > > Selection.Font.Bold = False
> > > With Selection.Font
> > > .ColorIndex = xlAutomatic
> > > .TintAndShade = 0
> > > End With
> > > Range("A1").Select
> > > 'Loop through range
> > > For bRow = 3 To 20
> > > For cRow = 3 To 99
> > > If Cells(bRow, "B").Value = Cells(cRow, "C").Value Then
> > > Cells(bRow, "B").Font.Color = vbRed
> > > Cells(bRow, "B").Font.Bold = True
> > > Exit For
> > > End If
> > > Next cRow
> > > Next bRow
> > >
> > > End Sub
>
> > > Paul
>
> > > ________________________________
> > > From: Mark Livingstone <namematters...@msn.com>
> > > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > > Sent: Wednesday, October 14, 2009 12:44:12 PM
> > > Subject: $$Excel-Macros$$ How to match numbers from 2 columns with
> > > minimal formulas
>
> > > Hi.
>
> > > I've been trying to put together VBA code that will allow me to match
> > > 2 columns with minimal Excel formulas.
>
> > > There are 2 columns. 1st column (B3 to B20) has a set of numbers.. 2nd
> > > column (C3 to C99) has another set of numbers. I need to highlight
> > > cells in the 1st column IF the cell's content matches any of the
> > > numbers in the 2nd column.
>
> > > Ideally, I would like to use VBA code only. Could anyone please cook
> > > something up or provide a code that already exists?
>
> > > Thanks!!!- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
