Excel VBA for conditional formatting.

Sub CreateConditionalFormatting()


Dim column As String
column = "C"
Dim NoOfRowNeeded As Integer
NoOfRowNeeded = 3000
Dim j As Integer
Dim cellSelect As String
For j = 1 To NoOfRowNeeded

cellSelect = column & CStr(j)

Range(cellSelect).Select
Selection.FormatConditions.Delete
If (j - 1) < 1 Then


Range(cellSelect).Interior.ColorIndex = 4
Else

'maximum 3 formula allowed.
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INT(" & column & CStr(j - 1) & ") Selection.FormatConditions(1).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INT(" & column & CStr(j - 1) & ")>INT(" & column & CStr(j) & ")"
Selection.FormatConditions(2).Interior.ColorIndex = 6

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=INT(" & column & CStr(j - 1) & ")=INT(" & column & CStr(j) & ")"
Selection.FormatConditions(3).Interior.ColorIndex = 4




End If
Next j


End Sub

Comments

Alex said…
One day I ran into like trouble and fortunately only due to the one tool I could solve it. This program was found out at a soft portal. The program helped me and besides would be usable for almost every difficult condition - recover Excel file tools.
Mar 9, 2011, 1:17:00 PM
Post a Comment

Popular posts from this blog

Clearcase check in/uncheck out all the checked out files script

I am so used to source safe recursive check in and found troublesome in clearcase having to do it manually one by one.

This is the Unix script that I have come out with

//for undo check out all the checked out files
cleartool lsco -cview -avobs | cleartool unco -rm `awk '{ print $(5) }' | sed -e 's/^\"*//' -e 's/ *\"$//'`
//for check in all the checked out files
cleartool lsco -cview -avobs | cleartool ci -nc `awk '{ print $(5) }' | sed -e 's/^\"*//' -e 's/ *\"$//'`


//Using xargs

cleartool lsco -cview -avobs -short | xargs cleartool ci
Read more

MSSQL GROUP_CONCAT

This is using XML feature of the SQL server. (nothing new here)


select Type, RestaurantNames from Restaurant AS A CROSS APPLY
(SELECT RestaurantName + ',' FROM Restaurant AS B WHERE A.Type = B.Type FOR XML PATH('')) D (RestaurantNames) GROUP BY Type, RestaurantNames


This is to get something like below (similar to MYSQL GROUP_CONCAT)


Type |RestaurantNames
----- ---------------
Chinese Food | Ah Yat Abalone, Liang Yah Yong Tau Foo,
Indian Food | Kanna Curry House,
Western Fast Food | Burger King, McDonald

instead of multiple rows.

like
Type |RestaurantName
----- ---------------
Chinese Food |Ah Yat Abalone
Chinese Food |Liang Yah Yong Tau Foo
Indian Food |Kanna Curry House
Read more

Duplicating a local copy of project from TFS and making web software client factory to work

My team members were asking me about how come their files can't be modified and etc even if it is a duplicated local copy. I tried it out and realized it is indeed not working as told. My finding was that the TFS has a tight control on the versioning of the file, even if the files are offline or those files are duplicate copies of the original files, the TFS control still present in every aspects of the actions you want to do onto those files.

So, it seems like there need to have an official declaration to inform Visual Studio that the project files are out of the TFS radar. After some internet searches and trials, it seems like to "permanently" detach project file from TFS are not so straightforward and not properly documented too. It requires some kind of hacking seems.

Below are the steps required after the files get copied out from the TFS.

1. Change all the files from readonly to non-readonly.
2. Remove your local workspace from the Visual Studio.
Go to menu it…
Read more