Change formatting on one sheet when text is typed in another

[tcfd1166]

I am trying to have one sheet with company names on it and another as a schedule. I have about 20 companies that I need to list on the main schedule sheet. Is there anyway when I type a company name in one sheet, when it copies that cell to the schedule sheet, format that cell to a specified format? I know conditional formatting will work, but it only allows 3. I need around 20. I would just like to change the background color of that cell when the name is typed in.

 

[venkat1926]

try this EVENT code
rightlcick sheet1 tab and click view code
there you copy/paste the macro given below

now type any name in column 1 IN SHEET 1 and hit ENTER
see what happens in sheet2

if ok modify the code statements to suit you.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Dim dest As Range
With Worksheets("sheet1")
Target.Copy
End With
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
dest.PasteSpecial
dest.Font.Bold = True
End With
End Sub

 

[venkat1926]

try this EVENT code
rightlcick sheet1 tab and click view code
there you copy/paste the macro given below

now type any name in an empty cell in column 1 IN SHEET 1 and hit ENTER
see what happens in sheet2

if ok modify the code statements to suit you.
the code statement giving format(font) may be changed to what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Dim dest As Range
With Worksheets("sheet1")
Target.Copy
End With
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
dest.PasteSpecial
dest.Font.Bold = True
End With
End Sub

 

[tcfd1166]

Maybe you misunderstood me. I have sheet 2 that I input information into. Sheet 2 information is copied into Sheet 1, but when the information is displayed in sheet 1, I would like it to color the background a different color for each different text that is inputted in sheet 2. Sheet 2 can have upto 20 different entries. I want these entries when shown on sheet 1 to be a different color, say A1:A4 corrensponds to "test". A5:A8 corresponds to "speak". A9:A11 corresponds to "test" again. I want in Sheet 1 when "test" is entered, the background to be green. When "speak" is entered, I want the background to be blue. Does this make sense?

 

[venkat1926]

I have sheet 2 that I input information into. Sheet 2 information is copied into Sheet 1, but when the information is displayed in sheet 1

my macro does copy from sheet 1 to sheet2. your requirement is from sheet 2 to sheet1. I am sure you will be able modify this macro for this.

I would like it to color the background a different color for each different text that is inputted in sheet 2.

dont do conditionfal fomatting. use a new macro given below
If you dont want that BOLD format
remove the line
dest.font.bold=

use this other macro for coloring

Sub coloring()
Dim rng As Range, c As Range

Set rng = Range([a1], [a1].End(xlDown))

For Each c In rng
If c = "test" Then c.Interior.ColorIndex = 4
If c = "speak" Then c.Interior.ColorIndex = 5
Next
End Sub

you have not told whether you want only that cell to be colored or the entire row.
I presume you want only the cell containing text like "test" to be colored;.
If you want entire row to be colored change the line
If c = "test" Then c.EntireRow.Interior.ColorIndex =

if you want actual numbers for different colors

in the vbeditor see help under "PatternColorIndex Property"

I wonder whether human eye can distinguish 20 colors.
In embedded if you cannot have more than seven ifs. but here ifs are not embedded so I think you can have 20 ifs. anyhow experiment.

I am sure you are familiar with macro codes and you will be able to modify to suit your needs. only skeleton macros are given.
greetings.

 

[tcfd1166]

I am trying to figure out this section of code here. This one is not working either.

Private Sub Worksheet_Change(ByVal Target As Range)
'Check Each Cell In Range
For Each Cells In Range("C3:C54,G3:G54,K3:K54,O3:O54,S3:S54,W3:W54,AA3:AA54,AE3:AE54,AI3:AI54")
MyText = Cells.Value
'Set Interior Color Based On Contents
Select Case MyText
Case "Bird"
Cells.Interior.ColorIndex = 3 'Red
Case "Fish"
Cells.Interior.ColorIndex = 6 'Yellow
Case "Dog"
Cells.Interior.ColorIndex = 4 'Green
'No Interior Color If Anything Else
Case Else
Cells.Interior.ColorIndex = -4142
End Select
Next
End Sub

Any clues?

 

[venkat1926]

1. dont use "cells" as variable. This is already has a meaning in excel vba. cells(1,1) means first row first column cell i.e. A1. use something like ccells
2. if at the top "option explicit" line is there you have to define the variable e.g.
dim ccells as range
dim mytext as string
3. the case is "case sensitive"
the entry in C3 (and other cells) should be Bird and not bird.
4. It is not necessary (but not wrong) to have mytext. You can use
select case ccells.

your macro should be like this. try now.

Sub test()
Dim ccells As Range
Dim mytext As String

'Check Each Cell In Range
For Each ccells In Range("C3:C54,G3:G54,K3:K54,O3:O54,S3:S54,W3:W54,AA3:AA54,AE3:AE54,AI3:AI54")
mytext = ccells.Value
'Set Interior Color Based On Contents
ccells.Select
Select Case mytext
Case "Bird"
ccells.Interior.ColorIndex = 3 'Red
Case "Fish"
ccells.Interior.ColorIndex = 6 'Yellow
Case "Dog"
ccells.Interior.ColorIndex = 4 'Green
'No Interior Color If Anything Else
Case Else
ccells.Interior.ColorIndex = -4142
End Select
Next
End Sub

 

[tcfd1166]

Modifications

Is there any way to combine these where if a certain part of the Case is entered, everything following in the " " can be that value? This is the coding that I am using.

Basically, using:
Select Case ccells
Case "Sample"
ccells.Interior.ColorIndex = 34
Case "Sample Type"
ccells.Interior.ColorIndex = 34

Can I have it where if Sample is in " ", anything else in " " with it would also be that same color instead of typing all the different variations of Sample?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ccells As Range

'Check Each Cell In Range
For Each ccells In Sheets(1).Range("F6:F69")
mytext = ccells.Value
'Set Interior Color Based On Contents
'ccells.Select
Select Case ccells
Case "Sample"
ccells.Interior.ColorIndex = 34
Case "Sample Type"
ccells.Interior.ColorIndex = 34
Case "Sample Text"
ccells.Interior.ColorIndex = 34
Case "Sample Lettering"
ccells.Interior.ColorIndex = 34
'No Interior Color If Anything Else
Case Else
ccells.Interior.ColorIndex = -4142
End Select
Next
End Sub

Also, is there any way I can enter this code on just one sheet and all the sheets use it? or do I have to enter it on every one (I have 10 that use this code).

 

[venkat1926]

CASE ELSE can be used. in vb editor type "select case" in the immediate window highlight the phrase and hit F1 and you will get help there you see the example

If you want to use the macro for a number of files
you park the macro in personal.xls file
when you open any excel invariably a hidden fiel Personal.xls is also opened. click windows(menu) and click unhide the persoal.xls will open. In the vbeditor of THIS (personal.xls) file you park the macro, save the personal.xls file and hide it again. the macro is available for use for any file.

 

[tcfd1166]

Update

Maybe my text is misunderstood.

I have more than just "Sample" in my coding.

Can I have it where anything after Sample in the same line will code to that color? Anything within the " " with the example Sample or Fish can be that color without having to add another Case for "Sample Type" or "Fish Type", if that helps.

I agree that Case Else would work, and I am using it if the text does not match the Case, but I want variations of the Case to be the same color, and now I have a lot of them to enter. Just trying to simplify...

Case "Sample"
ccells.Interior.ColorIndex = 34
Case "Sample Type"
ccells.Interior.ColorIndex = 34
Case "Fish"
ccells.Interior.ColorIndex = 34
Case "Fish Type"
ccells.Interior.ColorIndex = 34