Thread: Conditional formatting macro (highlight macro)

I have two problems with a macro which is essentially a highlight macro that used conditional formatting.

I have a workbook with 20 sheets (R1-R20). In those sheets I want to highlight row A-F based on the contents of F. This is simple enough, but for some reason I cannot have it accept conditional formatting on all twenty pages in one go (or actually it works fine, but not when I run the macro - the macro only works on the first page). It does however work if I do on every single page (20 times).

The code below is what I recorded, but which dowsn't work. It only inserts conditional formatting on sheet R1.

Code:

Sub highlight()
    Sheets(Array("R1", "R2", "R3", "R4", "R5", "R6", "R7", "R8", "R9", "R10", "R11", "R12", _
        "R13", "R14", "R15", "R16", "R17", "R18", "R19", "R20")).Select
    Range("F6:F28").Select
    Range("F28").Activate
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="5229"
    Selection.FormatConditions(1).Interior.ColorIndex = 43
    Range("C30").Select
    Sheets("R1").Select
    Range("C30").Select
End Sub

How do I make insert conditional formatting on every page (except for doing manually on every single page)?

Secondly I dont know how to get input for the macro. Preferrably I'd like a pop up box which would ask for the three possible conditions, i.e.
condition 1 -> color the row green
condition 2 -> color the row red
condition 3 -> color the row blue
The user would then enter the value for conditional check.
I'm clueless on how to do this.

/Møller

I want to color the cell green if the value is 5229.

The select is to unselect the selected cells (those that I apply the conditional formatting to). By selecting c30 (which contains no value) I make sure the user does delete something by accident.

I'm not saying it's beautiful code.

/Møller