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

Outlook : "operation failed, object could not be found. " when trying to close a PST.

Image
When you try to close a PST. The error message "operation failed, object could not be found" pop up. After you have tried scanpst and detect and repair. Still won't work. Solution. (a hard way) Start Regedit Go to Key HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\Microsoft Outlook Internet Settings Where "Microsoft Outlook Internet Settings" is the Outlook profile name that has problem. (note: Outlook support multiple profile even for same Windows login) In the HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\Microsoft Outlook Internet Settings, you will see a bunch of long string sub keys in it Like in the format of GUID, like e.g. 75d1fa940a0d3a4497130516edbde90e Then, start at the very first sub keys of HKCU\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\Microsoft Outlook Internet Settings. Right click on sub key, Select Find (menu) And then, ...
Read more

How to transfer app and data from old iPhone to new iPhone for model IOS17 and IOS18 above.

It always a headache for non tech savvy users to transfer their old iPhone app and data to new iPhone.  Despite all the effort by Apple with well thought out UX design to make it as intuitive as possible for users to do it themselves. It is still a hair pulling feat to take on. The worse part is that I notice Apple UX do not like to explain anything in written words, they would expect that you follow the flow of UX intuitvely. Many if not majority of iPhone users would have problem doing this easily perhaps their intuition is not the same as Apple employee most of the time. I checked Apple website on how to do it, there is also no clear instruction how to do it. Their instruction is simply - "trust my program process, i will get you there somehow". There is no transparency in the instruction unless you walk to Apple store in your town and talk to Apple employee to guide you. Comparing to Andoid phone, it seems like Apple are not so keen on helping their user to transfer data...
Read more

Tips on how to use IBM iNotes (web client) on emailing or scheduling - Like copy tables from Excel or checking for user id (windows logon id) through email address

There are certain functions that I accustomed with when I write email and schedule a meeting using mail client such as Ms Outlook or IBM Lotus Notes. However, such functions are not straightforward when I am in Lotus iNotes, a completely web based client application. Writing Email Common Functions 1. Checking for user id (Windows Logon ID) for the email address. There is no check for user detail in web based version. In the PC client version, you can do this by right clicking on the email address in the "To" textbox. However, there is another solution in web based verion. You can click on "To" hyperlink to search for that person in the address book. Then, find the person there and click "Details" there to get the user id (Windows Login ID) Similar, if you do not have email address of the person but you have the windows logon ID, you can just type the ID in the "To" textbox and click on search for that person. The system will reverse...
Read more