Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
Is there anyway to apply cell color formatting for text entered. I have about 20 different text that I need to list and would like each different text to be a different background color. Is there anyway known to do this? The text is entered in one sheet and automatically copied to another sheet as a summary. The summary sheet needs the colors applied only to it.
Use the Fill option on the formatting toolbar. The icon looks like paint being poured from a can.
Life's more painless for the brainless.
0 
Maybe I need to make myself clear...
I want a text value in one sheet which is automatically copied to a "summary" sheet to have a background color of "x" when the text meets certain criteria. I have about 20 different criteria. I know conditional formatting will work, but it only does 4. I need 20+. Any way this could work?
0
I guess I was way offbase with that one.
Perhaps something here will help:?
http://www.everyzing.com/results.js...
Life's more painless for the brainless.
0
Right click the tab for the worksheet where you want the colors, choose View Code and paste in your version of this code. The code will run whenever there is any change to the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
'Check Each Cell In Range
For Each cell In Range("A3,B4,C5")
MyText = cell.Value
'Set Interior Color Based On Contents
Select Case MyText
Case "Fire"
cell.Interior.ColorIndex = 3 'Red
Case "Duck"
cell.Interior.ColorIndex = 6 'Yellow
Case "Grass"
cell.Interior.ColorIndex = 4 'Green
'No Interior Color If Anything Else
Case Else
cell.Interior.ColorIndex = -4142
End Select
Next
End Sub
0
I tried this code, but it is not working. Could it be because I would like it to be in cells in these columns and rows? But I want them treated as individual cells, since the contents may change. C3:C54, G3:G54, K3:K54, O3:O54,S3:S54,W3:W54,AA3:AA54,AE3:AA54,AI3:AI54.
0
First: You do realize that my code was just an example, right? You would need to replace the range I used ("A3,B4,C5") with the range you want to check. You will also need to replace the 3 Case examples I used with the 20 Cases you want to check and choose an appropriate ColorIndex number for each case.
Second: Please explain this line to me:
"The text is entered in one sheet and automatically copied to another sheet as a summary."
Certain changes to a worksheet do not cause the Worksheet_Change event to run. If you'll tell me how the text is "automatically copied" perhaps that would help us find the problem.
0
Cell C3 value on sheet1 is ='sheet2'!B3. Does that help?
I understand the ranges need to be changed, but what I was wondering was can I put it to a specific range (ex. C3:C54) or does it have to be each cell?
0
If I understand you correctly, manual entries are made to Sheet2 and reflected in Sheet1 via formulae like ='sheet2'!B3.
When formulae in Sheet1 (='sheet2'!B3) are evaluated based on a change to Sheet2, the Worksheet_Change code stored in Sheet1 will not fire. However, if you put the code in Sheet2 then it will fire when the manual changes are made to Sheet2. The only change(s) we have to make is to the range it will work on. Instead of it working on ranges where the change was made (Sheet2), we want it to work on ranges in Sheet1.
Change the For Each line of code to read as follows, put the code in Sheet2 and it should work for you - after the next change in Sheet2.
For Each cell In Sheets(1).Range("C3:C54, G3:G54, K3:K54, O3:O54,S3:S54,W3:W54,AA3:AA54,AE3:AA54,AI3:AI54")
BTW - The Dad in DerbyDad03 makes me say that instead of asking if the ranges could be changed from "A3,B4,C5" to "C3:C54, G3:G54,etc." you should try it yourself and see. It's amazing what a little experimentation will teach you.
One more thing: The code will check all the values in the specified ranges in Sheet1 every time any change is made in Sheet2. If this impacts the performance of the workbook, there are ways to fix this by running the complete check only when a change that matters occurs. i.e. it will only run when changes are made to specific ranges/cells in Sheet2. If that is required, look up "target" in VBA Help for more info. I can help more if you need it.
Bonus Code:Here's something that might help with your project. Run it in a new worksheet since it will change the values in Column A.
Sub ColorIndexValues()
Columns(1).HorizontalAlignment = xlCenter
For Rw = 1 To 56
Cells(Rw, 1) = Rw
Cells(Rw, 1).Interior.ColorIndex = Rw
Next
End Sub
0
Hi,
Going on what you just said, which I found useful.
Is there a way, to flag for example the entire Row in a certain colour if one of the cells is orange?
for example.
Once a change request is submitted, thats means that the requester needs to action that request.
I have already programmed it with the above coding to change the colour to orange once the text states CR Approved. Then after this, I would then like the entire row. A5, L5, in red so that it can be seen as work to do.
Substance is the key to your character....
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
