I am trying to conditional format( Colours) a range of cells( C8:
AG8) to colour of another cell ( AH1) based on value in the range = value of (AH1).

Tried googling around and search the forum Can't seem to find something close. Need urgent help on these. Very new to this but required.
Found an example it is sort of what i intend and changed it but is seems not working. Help Please.

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.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case activesheet.cells("AH1").value
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

Here is a sample of some working conditional format code.

Sub conditional_Format_2()
Range("AG10").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$AG$11>25"
Selection.FormatConditions(Selection.FormatConditi ons.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.799981688894314
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

This was written in Excel 2007.
What version of Excel are you using?

I am using Excel 2003. I can't seem to understand your code. I have attached a sample file of what i actually whant to do. Any Help Please.

[quote=Bilbo_Baggins_Esq;793288]Here is a sample of some working conditional format code.

If the Legend was in a single row or column then you could use the Match function something like

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myindex As Integer

myindex = Application.WorksheetFunction _
.Match(Target.Value, Worksheets(1).Range("c25:f25"), 0)
Target.Interior.Color = Cells(25, 2 + myindex).Interior.Color


End Sub

Brian

Thanks. do you know how to point to only a range of cells to be changed and not all cells.


If the Legend was in a single row or column then you could use the Match function something like



Brian

The only way I can think of looks a bit crude, I couldn't think of how to use Target.address and check that it was in a Range, but I'm perhaps amore knowledgeable person than I can.

I had to use

If Target.Row >= 2 And Target.Row <= 21 And Target.Column <= 10 Then

myindex = Application.WorksheetFunction _
.Match(Target.Value, Worksheets(1).Range("c25:f25"), 0)
Target.Interior.Color = Cells(25, 2 + myindex).Interior.Color

End If

using your range a2:j21 as my example.

Brian

Thank you very much. I knew this forum is full of helpful knowledge people


The only way I can think of looks a bit crude, I couldn't think of how to use Target.address and check that it was in a Range, but I'm perhaps amore knowledgeable person than I can.

I had to use

If Target.Row >= 2 And Target.Row <= 21 And Target.Column <= 10 Then

myindex = Application.WorksheetFunction _
.Match(Target.Value, Worksheets(1).Range("c25:f25"), 0)
Target.Interior.Color = Cells(25, 2 + myindex).Interior.Color

End If

using your range a2:j21 as my example.

Brian