color coding values based on values in multiple sheets vba script

Copper Contributor

I am trying to color values in cells based on certain range criteria.

I do a lot of data entry on a daily basis. I came across a lot of  VBA related things and macro recorder etc in excels. However I could not get my desired job done properly. I am attaching my sample workbook.

I have to color the values in column F , the criteria for coloring is mentioned in cells H77 to H91 

and small table is made from C77 to F92 which is the criteria of my coloring.

Many a times the range of columns/items whose data entry is to be done is different in different excels. But more or less  it is like this find average in column F =value in E/value in D.

if value item=gokia and value of cell in column F is `>=5` or `<=7` then color it red

and count it in cell F78

if value of cell in column F is `>7` and `<=8` then color yellow 

if value of cell in column F is `>8` and `<=9` then color white 

count it in cell F78 

if value of cell in column F is `>9` then color it green and count it in cell F78.

and so on for different items this criteria is different.

There are 3000 excels in which I have to do this conditional formatting.
So opening an excel each time and manually doing conditional formatting is a repetitive task it takes a lot of time.
I want to automate that.






Sub Macro2()
' Macro2 Macro

    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub 






End Sub 



I am not able to run this kind of code in my excels successfully. Please see the sheet and let me know what correction should I make. The range in different excels is differet.


1 Reply


I believe the code is the first half of the project. The second half would be to create a LOOP in the macro that can open each of the 3,000 files and import the macro in the loop.


Can you confirm if all files you have follow the same naming structure, layout and table dimensions?