Use vba to format a cell green if >87.

[haxxbb]

Im making a report manualy today and i would like a vba to do it automaticly. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have want to have a conditional formatting on cells e8 to e35 to green if value over 77<o></o>
And another conditional formatting on cells f8 to f35 to green if over 90. <o></o>
<o> </o>
Seems fine enoght.. This is the reason why im posting here. <o></o>
<o> </o>
Format 1 if value in celle 8 to 35 is equal or over 77 set color green in cell. Its suppose to do this in colum e, h k and all the way to IO. So do one colum with format 1 ignore the next 2 colums, then do 1, ignore the next 2. Etc <o></o>
<o> </o>
Format 2 if value in celle 8 to 35 is equal or over 90 set color green in cell. Its suppose to do this in colum f, i l and all the way to IP. So do one colum with format 2 ignore the next to colums, then do 1, ignore the next to one. Etc <o></o>
<o> </o>
Cells in row 7 have value (in text) other then x and y, if you gonna use that please just write “xvalue” “xvalue” or something.<o></o>
<o> </o>
See pic it its still unclear. <o></o>
<o> </o>
http://img685.imageshack.us/i/mrexcel.png/<o></o>
<o> </o>
Thanks in advance<o></o>

 

[stnkynts]

Sounds like Conditional Formatting will work. Don't see why you need a macro.

 

[AlphaFrog]

Sub CF_Green()

    Range("E8:F35").FormatConditions.Delete
    
    With Range("E8:E35")
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="77"
        .FormatConditions(1).Interior.ColorIndex = 35 'Green
    End With
    
    With Range("F8:F35")
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="90"
        .FormatConditions(1).Interior.ColorIndex = 35 'Green
    End With
    
    Range("D8:F35").Copy
    Range("G8:IP35").PasteSpecial xlPasteFormats, xlNone, False, False
    Application.CutCopyMode = True
    
End Sub

 

[lenze]

Hi and welcome to the board!!!
Two rules for E8:IP35

=($E8>77)*MOD(Column(),3)=2

=($F8>90)*(MOD(COLUMN(),3)=0)

lenze

 

[haxxbb]

Sub CF_Green()
 
    Range("E8:F35").FormatConditions.Delete
 
    With Range("E8:E35")
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="77"
        .FormatConditions(1).Interior.ColorIndex = 35 'Green
    End With
 
    With Range("F8:F35")
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="90"
        .FormatConditions(1).Interior.ColorIndex = 35 'Green
    End With
 
    Range("D8:F35").Copy
    Range("G8:IP35").PasteSpecial xlPasteFormats, xlNone, False, False
    Application.CutCopyMode = True
 
End Sub

thanks for the kode! But it didnt work. Seems to be working fine at the start, but later i puts the color in the wrong colums etc.

 

[haxxbb]

Hi and welcome to the board!!!
Two rules for E8:IP35

=($E8>77)*MOD(Column(),3)=2

=($F8>90)*(MOD(COLUMN(),3)=0)

lenze

Im a newbie when it comes to this. Where do i put the code? i have tryed running it as a macro.

Thanks in advance.

 

[lenze]

The above are Formulas, not code. They are to be used in conditional formatting. What version of Excel are you using??

lenze

 

[haxxbb]

2007

 

[lenze]

Highlight the cells E8:IP35. On the ribbon, choose Contional Formatting. Click new rule. Select "use a Formula to detemine which cells to format". Copy one of the formulas to the field. Repeat for the 2nd formula!!

lenze

lenze

 

[haxxbb]

I have already tryed this. It does not work. I get a formula error.

Do you have any tips?