PDA

View Full Version : [SOLVED] Cell formating



TGwilding
06-20-2005, 02:55 PM
Hello,
I would like to learn a code that formats a row based on the condition =
if(cell="K",color red,if(cell="G",color blue,if(cell="N",color orange,if(cell="M",color grey,if(cell="R",color green,color black)))))
I know I can use conditional formating, however, I need more than 3 conditions.
Does anyone know a code that will do this?
the colors I wish to use are blue, orange, red, green, dark grey.
thanks

Jacob Hilderbrand
06-20-2005, 03:18 PM
Try here.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=90

TGwilding
06-20-2005, 04:04 PM
Thanks for the help. I am almost there. I have it now so that the cell changes to the associated color if there is either "Kevin", "Mark", etc in it, but I would like the whole row to change to that color if "Kevin", Mark", etc appears in any row in column A. ie, if "kevin" is in cell A7, then every cell in row seven turns that same color. you helped somone wout with this in another posting, but I can't tweak it to do what I want. The code you wrote is below:


Dim x As Integer
Dim LastRow As Integer
LastRow = Range("A65536").End(xlUp).Row
For x = 7 To LastRow
If Range("AB" & x) = "x" Then
Range("A" & x & ":AG" & x).Interior.ColorIndex = 15
Range("AC" & x).Value = "=Sum(AD" & x & "-AD" & x & ")"
Exit Sub
Else
End If
Select Case Range("AC" & x).Value
Case 0
Range("A" & x & ":AG" & x).Interior.ColorIndex = 0
Range("AC" & x).Value = "=Sum(D2-AD" & x & ")"
Case Is <= 29
Range("A" & x & ":AG" & x).Interior.ColorIndex = 0
Case Is <= 49
Range("A" & x & ":AG" & x).Interior.ColorIndex = 36
Case Is > 49
Range("A" & x & ":AG" & x).Interior.ColorIndex = 3
End Select
Next x
End Sub


The code I am working with is below:


Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Font.ColorIndex = xlNone
Case "Kevin"
Cell.Font.ColorIndex = 3
Case "George"
Cell.Font.ColorIndex = 4
Case "Mark"
Cell.Font.ColorIndex = 5
Case "Ned"
Cell.Font.ColorIndex = 6
Case "Russ"
Cell.Font.ColorIndex = 7
End Select
Next
End Sub


any help would be appreciated.
thanks

Jacob Hilderbrand
06-20-2005, 04:07 PM
Change


Cell.Font.ColorIndex = 3

To


Cell.EntireRow.Font.ColorIndex = 3

TGwilding
06-20-2005, 04:16 PM
Thanks. That worked perfectly

Jacob Hilderbrand
06-20-2005, 05:05 PM
You're Welcome :beerchug:

Take Care