Need Help with VBA conditional formatting

sadiqsabia

Registered User.
Local time
Today, 13:08
Joined
Sep 3, 2008
Messages
18
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.

Code:
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.

Code:
Sub conditional_Format_2()
    Range("AG10").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$AG$11>25"
    Selection.FormatConditions(Selection.FormatConditions.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.

Here is a sample of some working conditional format code.
 

Attachments

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
 
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

Code:
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

Code:
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
 

Users who are viewing this thread

Back
Top Bottom