Automatic colors?

[mrkris1982]

Is there anyway I can hard-code the color of each row?

Id like to alternate white with tan (office 2003) if possible.

The shared workbook we use now is colored manually. When pasting, the formats paste as well and I wonder if hard coding it will prevent the format from being pasted. I DO realize you can paste special, but I am not going to put in the time to show that to the employees using the workbook since I could just go back to an all WHITE workbook.

Thanks

 

[mrkris1982]

kpark91 has code that I really like because as text is entered into a row, the row changes color to white (for odds) and blue) for even rows. Id like to keep that feature and when I tried CF in a test workbook, I see that the entire sheet is colored that way.

Anyway, my three conditional formats for column F only are:

Formula is =ISNUMBER(SEARCH("*defer*",F217)) cell turns yellow/black text

Cell value is ="Support Contacted" cell turns red/black text
Cell value is ="Left Message(s)" cell turns bright green black text

 

[lenze]

So adjust the MOD formula

=(MOD(ROW()-2,2)+1<=1)*(A2<>"")

lenze

 

[mrkris1982]

I still need my three conditional formats. Where would I put this code? Are you saying to replace all the code previously pasted?

 

[lenze]

I don't know about your code!!
How are the valeus in Column "F" entered??
lenze

 

[mrkris1982]

The text in column F derives from data validation list.

When you say adjust the MOD formula, I am just ocnfused as to where I am suppose to put it.

This is the only place I see MOD in the existing code I have:

If n Mod 2 = 1 Then
                Rows(c).Interior.Color = RGB(153, 204, 255)

Shoule it read:

If n Mod 2 =(MOD(ROW()-2,2)+1<=1)*(A2<>"")

??

 

[lenze]

You are confusing 2 different approaches to your problem. KParks is a MACRO. My suggestion is to use Condtional Formatting, NOT a macro. But, with your conditions in Column "F" we can use a macro there. So in the WorkSheet module,(RightClick the sheet Tab and choose "View Code"), enter this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 6 Then Exit Sub
If InStr(Target, "defer") Then
    Target.Interior.ColorIndex = 6
Else
    Select Case Target
        Case Is = "Support Contacted"
            Target.Interior.ColorIndex = 3
        Case Is = "Left Message(s)"
            Target.Interior.ColorIndex = 4
        Case Else:Target.Interior.ColorIndex = 15
    End Select
End If
End Sub

This will format Column "F" accordingly when a selection is made
Now, In CF,(Format>Conditional Formatting), with A1 active, select the eniter sheet$A1<>"").Remove the CF from Column "F"
Sounds complicated, but it will work.
lenze

 

[mrkris1982]

Cool. I did everything you said. I removed kparks macro code and inserted yours.

Removed all three CF's

Added the following CF:

=(MOD(ROW()-2,2)+1<=1)*(A2<>"")

Im not getting the rows to alternate colors though...not sure whats up.

 

[lenze]

Did you foll my instructions? Your formula should be

=(MOD(ROW()-2,2)+1<=1)*($A1<>"")

Also, this line of the code

Case Else:Target.Interior.ColorIndex = 15

should be

Case Else: If Target.Row MOD 2 = 0 Then Target.Interior.ColorIndex = 15

HTH
lenze

 

[mrkris1982]

Here is what I have for code:

If Target.Count > 1 Then Exit Sub
If Target.Column <> 6 Then Exit Sub
If InStr(Target, "defer") Then
    Target.Interior.ColorIndex = 4
Else
    Select Case Target
        Case Is = "Support Contacted"
            Target.Interior.ColorIndex = 3
        Case Is = "Left Message(s)"
            Target.Interior.ColorIndex = 4
        Case Else: If Target.Row Mod 2 = 0 Then Target.Interior.ColorIndex = 6
    End Select
End If
End Sub

Already have the worksheet_change name, so thats why its not here

For CF, I do exactly as you say...highlight A1 through J1999, go up to CF and enter Formula Is

=(MOD(ROW()-2,2)+1<=1)*([B][COLOR=red]$A1[/COLOR][/B]<>"")

I have it set to change the cell to light blue, but when Im not getting those results.