LinkBack URL
About LinkBacks
Hi friends, kindly help us with a VBA code for conditional formatting as mentioned in the title, attached the sample sheet for the reference, where we need to pull the colors is mentioned.
The coding should apply in G and H column in the background.
Hi friends, kindly help us with a VBA code for conditional formatting as mentioned in the title, attached the sample sheet for the reference, where we need to pull the colors is mentioned.
The coding should apply in G and H column in the background.
are you looking for VB code or conditional formatting through Excel?
________________________________________________________
If your problem is solved, update the thread as SOLVED: Go to the top of the first post-Select Thread Tools-Select Mark thread as Solved OR - Go to the first post - Click edit- Click Advance- Just below the word "Title:" you will see a dropdown with the word No prefix.- Change to Solve- Click Save.
Show your gratitude to the person who helped you solving your problem by clicking on star button at the bottom of such post.
If you are using Excel 2003, you can add only 3 conditions to any cell. Not more than that.
I need a VBA code and I use excel 2010
FWIW:
Sub antoanand1981() Dim rcell As Range For Each rcell In Range("I2:J" & Range("J" & Rows.count).End(3)(1).Row) Select Case rcell.Value Case Is = "G" rcell.offset(, -2).Interior.ColorIndex = 4 Case Is = "Y" rcell.offset(, -2).Interior.ColorIndex = 6 Case Is = "O" rcell.offset(, -2).Interior.ColorIndex = 44 Case Is = "R" rcell.offset(, -2).Interior.ColorIndex = 3 Case Is = "NR" rcell.offset(, -2).Interior.ColorIndex = 16 Case Is = "ND", "NRW" rcell.offset(, -2).Interior.ColorIndex = 37 End Select Next rcell End Sub
Conditional formatting.jpg Attached an image for your reference
Please change the MS-Off version in your profile (showing as Excel 2003). Try the below code.
Sub Test() Lastrow = Range("G" & Rows.Count).End(xlUp).Row With Range("G2:G" & Lastrow) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=I2=$M$2" 'set formatting formula .FormatConditions(1).Interior.Color = 5296274 'add green color .FormatConditions.Add Type:=xlExpression, Formula1:="=I2=$M$3" 'set 2nd formatting formula .FormatConditions(2).Interior.Color = 65535 'add yellow color .FormatConditions.Add Type:=xlExpression, Formula1:="=I2=$M$4" 'set 3rd formatting formula .FormatConditions(3).Interior.Color = 49407 'add orange color 'continue for all other criteria 'you can get the color codes on net or record a macro setting them up End With With Range("H2:H" & Lastrow) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=J2=$M$2" 'set formatting formula .FormatConditions(1).Interior.Color = 5296274 'add green color .FormatConditions.Add Type:=xlExpression, Formula1:="=J2=$M$3" 'set 2nd formatting formula .FormatConditions(2).Interior.Color = 65535 'add yellow color .FormatConditions.Add Type:=xlExpression, Formula1:="=J2=$M$4" 'set 3rd formatting formula .FormatConditions(3).Interior.Color = 49407 'add orange color 'continue for all other criteria 'you can get the color codes on net or record a macro setting them up End With End Sub
HI All,
Thanks for the response, but I need the formatting done behind ma results. Check the below explanation
VBA codes for conditional formatting which has multiple criteria. Example Column A1:A10 has names, each names has three criteria from B1:D10 and Column E has achieved result for each names... results Should fetch in Column E. So the Macro or VBA codes should check each criteria and highlight Column in different colors with the results in the front.
Example: A1: Ramesh has achieved 98, B1:D1 = (B1=99,C1=95,D1=90), Result is 98 with Yellow background
Formulas for example =if(E1>=B1,"Green",if(and(E1>=C1,E1<B1),"Yelow",if(and(E1>=D1,E1<C1),"Orange","Red")))
Note :Im Using Microsoft Excel 2010.
Please reply ASAP very Urgent.
Hope u got it. u can also mail me at [email protected] or let me know I can call u to fix this thanks.....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks