Conditional formatting

[priccijr]

I'm using the code below and was wondering what to modify if I need the entire row from A to H to change color depending on the value in column H?





Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit

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 "QUALITY"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "CRITICAL"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "MEDIUM"
Cell.Interior.ColorIndex = 6
Cell.Font.Bold = True
Case "LOW"
Cell.Interior.ColorIndex = 20
Cell.Font.Bold = True
Case "OK"
Cell.Interior.ColorIndex = 35
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub



TIA

-Pete

[austenr]

Something like this:

Case "CONDITION 1": Range("A1:H1").Interior.ColorIndex
= 15

[priccijr]

Thanks for the help austenr do I change my case "QUALITY" to "CONDITION 1"? I need the color of the row to depend on the text in Column H.

[austenr]

No CONDITION 1 was just an example. Sorry if it threw you.

[JimmyTheHand]

Try this simplified version of your code:

Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
Dim ColIx As Long

Set Rng1 = Range("H1", Range("H" & Rows.Count).End(xlUp))
For Each Cell In Rng1
Select Case Cell.Value
Case "QUALITY", "CRITICAL"
ColIx = 3
Case "MEDIUM"
ColIx = 6
Case "LOW"
ColIx = 20
Case "OK"
ColIx = 35
Case Else
ColIx = -4142 'xlNone
End Select
Cell.Offset(, -7).Resize(, 8).Interior.ColorIndex = ColIx
Cell.Font.Bold = (ColIx > 0)
Next
End Sub The idea is setting font to bold if ColorIndex>0, which might not be good if you change the color scheme or want to have colored cells with normal (i.e. not bold) characters. But with the current conditions the macro works fine.

[priccijr]

This works great thanks guys.

I have another add-on request to this code.

If I want to have only the OK's change color who's "F" cell matches the "F" cell that has Quality in "H".

EX.
change color of row 1 if H1="OK" then F1 matches F4 and H4="Quality"

I'm not sure how hard this would be but thank you in advance to anyone who can help me out.

[JimmyTheHand]

:hi:
It doesn't seem to be much of a work, but I don't see clearly what you really want. I think you should upload a sample workbook.

[priccijr]

Thanks for your help Jimmy

The file is attached.

The way it works is we pull data from a system and paste it into the raw data page the formula bar on top of the "Alarms Parsed" sheet gets copied down to parse the data over this is were the code goes to work.

What I'm looking to do is when a Tagname in "F" has a "Quality" alarm in "H" it looks for the same tagname in "F" and "OK" in "H" and turns this row green.

I'm looking for the OK for that Quality alarm and want the row green.

The way it works now it turns ALL the OK's green.

Thanks.

[austenr]

It would be helpful if you up loaded a workbook with some data (ficticious is ok). That way, it is easier for those wanting to help.

[priccijr]

I would like only the purple rows to turn green because they match with the two red quality alarms that were received.

[JimmyTheHand]

Something like this?

[priccijr]

A working solution is fine

[priccijr]

That works thanks for the "hand" Jimmy.