Code for Conditional Formatting

Brian from Maui

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
I am in need of some (lots) help. Presently to highlight the active row, I use CF and

=CELL("row")=ROW(), then using

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Application.ScreenUpdating = True
End Sub

What I want to combine with this is,

IF S3 equals "Active" color A3:T3 green

IF S3 equals "Closed" color A3:T3 red

IF S3 equals "Prospect" font color red in A3:T3

the range of the sheet is from A3:T300

TIA
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Brian

Does using a formula such as:

=AND(CELL("row")=ROW(),$S3="Active")

and then ones for "Close" and "Prospect" do what you want?

Richard
 
Upvote 0
Brian

How about this then - add additional formats as required:

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
Dim dRng As Range
Set dRng = Range("A3:T300")
If Not Intersect(Target, dRng) Is Nothing Then
    With dRng
        .Interior.ColorIndex = xlNone
        .Font.ColorIndex = xlAutomatic
    End With
    Select Case Cells(Target.Row, "S").Value
        Case "Active"
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")).Interior.ColorIndex = 4
        Case "Closed"
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")).Interior.ColorIndex = 3
        Case "Prospect"
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")).Font.ColorIndex = 3
        Case Else
    End Select
End If
End Sub

Hope this is what you want!

Richard
 
Upvote 0
Richard,

Much like Conditional Formatting, if the criteria is found in Column S, color A3:T3 instead of the active row being colored. In other words if the criteria is found, color that row even if I click in another row...
 
Upvote 0
Ah I see Brian - how about the following then:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = True 
Dim dRng As Range 
Set dRng = Range("A3:T300") 
If Not Intersect(Target, dRng) Is Nothing Then 
Select Case Cells(Target.Row, "S").Value 
        Case "Active" 
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")).Interior.ColorIndex = 4 
        Case "Closed" 
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")).Interior.ColorIndex = 3 
        Case "Prospect" 
            Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")).Font.ColorIndex = 3 
        Case Else
            With Range(Cells(Target.Row, "A"), Cells (Target.Row, "T"))
                 .Font.ColorIndex = xlAutomatic
                 .Interior.ColorIndex = xlNone
             End With
    End Select 
End If 
End Sub

This will only fire upon changes in the sheet (note it's using a different event. This also means that if you apply this to the sheet, it won't actually apply the formats until you have changed the S column cells. Hence, what you will need to do is either F2 and Enter down col S or use a Ctrl+H on columns S and search for and replce common occurences (given "Active", "Closed" and "Prospect" I would go for a Find and Replace on "e").

Hope this is OK!

Richard
 
Upvote 0
Richard,

Again a belated thank-you. It seems sleep and golf dominates my life right now! The code works beautifully! :lol:
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Conditional format to highlight future dates
Replies
3
Views
213
Joe4
Joe4
R
  • Question Question
Conditional Formatting with Color Gradient
Replies
3
Views
653
myall_blues
myall_blues
I
  • Question Question
Advice for conditional formatting not working
Replies
1
Views
316
rlv01
R
R
  • Solved
VBA highlight specific Cells in row
Replies
2
Views
242
RedOctoberKnight
R
L
  • Question Question
Code shuts down excel
Replies
3
Views
340
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,222,689
Messages
6,167,647
Members
452,127
Latest member
jayneecm

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top