Conditional Formating

Hello

I have a spreadsheet with the following columns

A. Risk ID
B. Description
C. Pre Mit
D. PostMit
E. Trend
F. State


Column A is a referance number.
Column B is a description.
Column C has a drop down menu with a list of 5 options, the following (including the numbers) 1.Very Low 2. Low 3.Medium 4.High 5. Very High
Column D has a drop down menu with the same options.
Column E Is a column that will show a trend using the windings format ì = Up arrow è = straight arrow î = Down arrow
Column F Will show a drop down menu with the following options each representing the current risk level for the risk. The options are
1G, 1A, 3R, 4B.






The FIRST problem that I am having is I would like column E to indicate a trend using the windings format ì = Up è = Straight î = Down Trend. I want
the trend to be based upon the difference between the two values in columns C and D. If column C is a lower risk level than column D then i would like
column E to show an Up trend (ì). If column C contains a higher risk than column D I would like a down trend (î). If both risk ratings are the same I would
like column E to show a straigt arrow (è).


Ok thats the first problem, I also need the entire row to change color based upon the selection of the F column.

I need the row to be Black is '4b' is selected
I need the row to be Red if '3R' is selected
I need the row to be Amber if '1A' is selected
I need the row to be Green if '1G' is selected.

The major problem i am having here is that i can only use 3 Conditional formats at a time with
=$F3="1G" PAST SPECIAL.

I hope someone can provide me with some assistance :)....bet mike spots this :)

copy this into cell D =IF(AND(C2<>"",D2<>""),IF(C2=D2,"è",IF(C2>D2,"ì","î")),"")
this is not a vba code it is just standard excel formulas

and paste this code in 'Microsoft Excel Objects' in the sheet taht tou are working in Private Sub Worksheet_Change(ByVal Target As Range)

Set isect = Application.Intersect(Target, Range("f2"))

If isect Is Nothing Then
Exit Sub
Else
If ActiveSheet.Cells(2, 6) = "4B" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveCell.Select
ElseIf ActiveSheet.Cells(2, 6) = "3R" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf ActiveSheet.Cells(2, 6) = "1A" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
ElseIf ActiveSheet.Cells(2, 6) = "1G" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If

End If

End Sub


i wrote the code just for row 2 so just adjust for more rows

Hello, the formula works great, however when ever i select one of the options from column F i recieve run time error '438' Object Doesnt Support this property or method. I have highlighted what it keeps highlighting. Thanks for your help on this its looking good.







Private Sub Worksheet_Change(ByVal Target As Range)

Set isect = Application.Intersect(Target, Range("f2"))

If isect Is Nothing Then
Exit Sub
Else
If ActiveSheet.Cells(2, 6) = "4B" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
ActiveCell.Select
ElseIf ActiveSheet.Cells(2, 6) = "3R" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf ActiveSheet.Cells(2, 6) = "1A" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
ElseIf ActiveSheet.Cells(2, 6) = "1G" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If

End If

End Sub

'End Subi wrote the code just for row 2 so just adjust for more rows

The question is wich office are u using i wrote the program in office 2007.

here is one for 2000
Private Sub Worksheet_Change(ByVal Target As Range)

Set isect = Application.Intersect(Target, Range("f2"))

If isect Is Nothing Then
Exit Sub
Else
If ActiveSheet.Cells(2, 6) = "4B" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
ElseIf ActiveSheet.Cells(2, 6) = "3R" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
ElseIf ActiveSheet.Cells(2, 6) = "1A" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
ElseIf ActiveSheet.Cells(2, 6) = "1G" Then
Rows("2:2").EntireRow.Select
With Selection.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
End If

End If

End Sub

If it still dont want to work just start recording a macro high light a row and change the color to what u want. then copy everyting from WITH... and paste in your code.