highlighting in Excel

[Guest]

Is there a function or procedure I might use to accomplish the following

I want to enter a "name", have excel check a table (within the spreadsheet say from A3 to H9) for that name, when found, it should highlight it in each cell that it is found

Thank you very much.

 

[Guest]

Hi Linda
You can use conditional formatting for this

1) Select A3 to H

2) Format>Conditional_Formatting on the pull down men

3) Cell Value is / equal to / Click on name cell or enter reference (ex. =$A$1

4) Click on Format button and select highlighting

Good Luck
Mark Graesse
(e-mail address removed)
Boston M
----- Linda wrote: ----

Is there a function or procedure I might use to accomplish the following

I want to enter a "name", have excel check a table (within the spreadsheet say from A3 to H9) for that name, when found, it should highlight it in each cell that it is found

Thank you very much.

 

[Don Guillett]

Here are some to try

Sub ColorItThree()
For Each cel In [colorlist]
For Each c In Range("a4:g40")
If Trim(c) = cel Or Trim(c) = cel & " Repeat" Then
c.Interior.ColorIndex = 46
c.Font.ColorIndex = 2
End If
Next c
Next cel
End Sub

Sub Colorit2() 'This also works like colorit
For Each cel In [colorlist]
With Worksheets("TV").Cells
Set c = .Find(cel, LookAt:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 46
c.Font.ColorIndex = 2
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next cel
End Sub

Sub Colorit()
Dim rng As Range, x As Range, i As Integer
Set rng = Sheets("TV").Cells 'Range("a1:ev150") 'Cells
On Error Resume Next
For Each c In [colorlist]
Set x = rng.Find(c, rng(rng.Count), LookAt:=xlWhole)
x.Interior.ColorIndex = 46 '5 '4 '36 '19
x.Font.ColorIndex = 2

If Not x Is Nothing Then
For i = 1 To Application.CountIf(rng, c) ' - 1
x.Interior.ColorIndex = 46 '5 '36 '19
x.Font.ColorIndex = 2
Set x = rng.FindNext(x)
Next
End If
Next c
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)

Is there a function or procedure I might use to accomplish the following:

I want to enter a "name", have excel check a table (within the spreadsheet

say from A3 to H9) for that name, when found, it should highlight it in each
cell that it is found.

 

[Bob Phillips]

Linda,

Use conditional formatting on the range A3:H9.

Select A3:H9
Goto menu Format>Conditional Formatting
Change condition 1 to Formula Is
Add a formula of =A3=$A$1 (note the $s, assuming name is in A1)
Click the Format button
Select the Patterns tab
Pick a colour
OK out

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Is there a function or procedure I might use to accomplish the following:

I want to enter a "name", have excel check a table (within the spreadsheet

say from A3 to H9) for that name, when found, it should highlight it in each
cell that it is found.

 

[Guest]

Bob
Thank you for your help. The formula you provided would work if I chose to highlight a name in ONE color. I have 5 different name that can appear a few times, each name MUST have it's own individual color

For example
Enter name: Mar

Moon Sun Venus Mars Moon Saturn Mar

(in the above case, mars appears twice, and it would be a red color, if I entered the name Sun, it would appear once but in Yellow
How can I manipulate your formula to do that using conditional formating?

 

[Bob Phillips]

Linda,

I am afraid with 5 you can't, 3 yes, but 5 is too many. For that you need
VBA. Here is a worksheet event that does what you require, but you will need
to set the criteria (Moon, Sun, etc.). Put this code in the worksheet code
module (right-click on the sheet tab name, select View Code from the menu,
and paste the code in)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A3:H9")) Is Nothing Then
With Target
Select Case LCase(Target.Value)
Case "mars": .Interior.ColorIndex = 3 'Red
Case "moon": .Interior.ColorIndex = 5 'Blue
Case "sun": .Interior.ColorIndex = 6 'Yellow
Case "saturn": .Interior.ColorIndex = 10 'Green
Case "venus": .Interior.ColorIndex = 45 'orange
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,
Thank you for your help. The formula you provided would work if I chose to

highlight a name in ONE color. I have 5 different name that can appear a few
times, each name MUST have it's own individual color.

For example:
Enter name: Mars

Moon Sun Venus Mars Moon Saturn Mars

(in the above case, mars appears twice, and it would be a red color, if I

entered the name Sun, it would appear once but in Yellow)

 

[Guest]

Bob

The code works, however, my intention is still to have the name of the planet highlighted when typed into a certain cell, and then having the program check the worksheet and highlight accordingly. As it stands now, I need to type the name of the planet on the work sheet itself to highlight it..

I'm sure I'm just missing one or two lines of code for that.....thanks so much for your hel

Linda

 

[Bob Phillips]

Linda,

Don't get what you are saying.

.... my intention is still to have the name of the planet highlighted when
typed into a certain cell ... this is exactly what the code does.

.... then having the program check the worksheet and highlight accordingly
.... why, it will already be highlighted.

Help me here, I am floundering.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,

The code works, however, my intention is still to have the name of the

planet highlighted when typed into a certain cell, and then having the
program check the worksheet and highlight accordingly. As it stands now, I
need to type the name of the planet on the work sheet itself to highlight
it...

 

[Guest]

LOL, I apologize for being unclear Bob. Let me try again

I would like to type the name of a planet, in one location say, E2. My active worksheet already contains a table listing all the planets during the days of the week (sample below). I would like the program to search the active worksheet for the planet i have entered in cell E2, when it finds it, I would like it to be highlighted in a specific color

The table looks like this

Sunday Monday Tuesday Wednesday Thurs Fri Sa
Sun Moon Mars Mercury Jupiter Venus Satur
Mercury jupiter Venus Sun Moon Saturn Mars

 

[Bob Phillips]

Sorry Linda, I think I lost track of the original thread (past my bedtime is
my only excuse).

Anyways, now I think I know what you want. My original CF was correct, just
limited. This is hopefully now correct, and not limited. we'll see...

Same as before, worksheet event code in the sheet module. It caters for 5
heavenly bodies, extend as required

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColour As Long
Dim cell As Range
Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("E2")) Is Nothing Then
With Target
Select Case LCase(Target.Value)
Case "mars": iColour = 3 'Red
Case "moon": iColour = 5 'Blue
Case "sun": iColour = 6 'Yellow
Case "saturn": iColour = 10 'Green
Case "venus": iColour = 45 'orange
End Select
For Each cell In Range("A3:H9")
If cell.Value = .Value Then
cell.Interior.ColorIndex = iColour
Else
cell.Interior.ColorIndex = xlColorIndexNone
End If
Next cell
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub


Just one thing confusing me now (such clear-headedness(sic!)), if there can
only be one set of values highlighted, as there is only one source value
(E2), why do you need different colours, I would have thought that as the
text differs, only one colour would be needed (just thought why mightwant
different colours, but try this first and see how we get on).

Forgive me if you post back and I don't reply tonight (this
evening/afternoon/morning - whatever it is in your part of the world), but I
must sleep, I will get to it tomorrow.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

LOL, I apologize for being unclear Bob. Let me try again.

I would like to type the name of a planet, in one location say, E2. My

active worksheet already contains a table listing all the planets during the
days of the week (sample below). I would like the program to search the
active worksheet for the planet i have entered in cell E2, when it finds it,
I would like it to be highlighted in a specific color.

The table looks like this:

Sunday Monday Tuesday Wednesday Thurs Fri Sat
Sun Moon Mars Mercury Jupiter Venus Saturn
Mercury jupiter Venus Sun Moon Saturn

Mars