Dec 20 2020 09:12 AM
Dec 20 2020 09:12 AM
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, _ Formula1:="=5" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 5296274 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False 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.
Dec 22 2020 11:58 AM
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?