Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



VBA Conditional Formatting in Microsoft Excel

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 21-01-2012
Member
 
Join Date: Jan 2012
Posts: 23
VBA Conditional Formatting in Microsoft Excel

I am trying to CONDITIONAL FORMAT RANGE OF CELLS BASED ON ANOTHER RANGE OF CELLS I.E IF THEY MATCH (CELL FOR CELL) no format if one cell different to its counterpart formatting applied. I have achieved this in office 2007's excel which works as i want however the pc this program is destined for runs on excel 2003 so the conditional formatting doesn't work. So im looking to create vba code for it but cant get my head around converting the working formula into code : NOT (E8:R31=E54:R77) SETS OFF A CELL FILL. Thanks in advance to anyone who can help out.

Reply With Quote
  #2  
Old 21-01-2012
Member
 
Join Date: Aug 2011
Posts: 572
Re: VBA Conditional Formatting in Microsoft Excel

From memory (of Excel 2003 and below) if you put the Conditional Format in the first cell and use the Format Painter on all subsequent cells it should work. Try the Conditional Format:=IF(E8<>E54,1,0) and format it accordingly. You may have to re-edit the Conditional Format formula to remove any $ automatically put in there by Excel - e.g. change "=IF($E$8<>$E$54,1,0)" to read as above. When you use the format painter on cells E8 thru to R31, it should increment the E8 and E54 in the Conditional Format formula for each cell individually (I think). If you do this in Excel 2007, it will just apply the same Conditional Format across the range you selected - gives the same result in Excel 2007, but may not translate back to 2003 properly.
Reply With Quote
  #3  
Old 21-01-2012
Member
 
Join Date: Aug 2011
Posts: 534
Re: VBA Conditional Formatting in Microsoft Excel

It is not easy to find the exact solution but something similar to that can put more highlight on the same. I know this vbcode which can help you to get the results you are

expecting :
Code:
Private Sub Worksheet_Change (ByVal Target As Excel.Range) 
 If Not Intersect (Target, Range ("E4: E23")) Is Nothing Then 
 With Target 
 Select Case Target.Value 
 Case Is <0.5 
 . Interior.ColorIndex = 3 
 Case 0.5 To 0.79 
 . Interior.ColorIndex = 44 
 Case 0.8 To 0.99 
 . Interior.ColorIndex = 6 
 End Select 
 End With 
 End If 
 End Sub
Reply With Quote
  #4  
Old 21-01-2012
Member
 
Join Date: Aug 2011
Posts: 684
Re: VBA Conditional Formatting in Microsoft Excel

I think this is possible with macro in Excel. For select the range [E4: E23] right click and choose format cells. Enables the Number tab and check the standard list Custom. In the Type field before [Red] [<0.49] Standard; [Orange] Standard. Let the Range [E4: E23] selected format and place, not Last conditional formatting. Fill in the fields of a condition to get the cell value is between 0.80 and 0.99. Click Format and click on Active Yellow. In condition 2 filled fields to obtain the cell value is between 1 and 1.33 and then click Format and select pattern in green and white police clicks. In the third condition value between 1.34 and 9.99. Active format click click click font and blue on white. And finally click OK twice. I hope you can take advantage of it.
Reply With Quote
  #5  
Old 21-01-2012
Member
 
Join Date: Aug 2011
Posts: 560
Re: VBA Conditional Formatting in Microsoft Excel

Here is another sample vb code. Try to have a look on the same
Code:
Option Explicit 
Private Sub Worksheet_Change (ByVal Target As Excel.Range) 
Dim cell As Range 
For Each cell In Range ("B2: H10") 
If cell.Value = "X" Then cell.Font.ColorIndex = 15 
If cell.Value = "L" Then cell.Font.ColorIndex = 3 
If cell.Value = "T" Then cell.Font.ColorIndex = 41 
If cell.Value = "R" Then cell.Font.ColorIndex = 10 
If cell.Value = "R" Then cell.Font.FontStyle = "bold" 
If cell.Value = "E" Then cell.Font.ColorIndex = 15 
If cell.Value = "F" Then cell.Font.ColorIndex = 3 
If cell.Value = "D" Then cell.Interior.ColorIndex = 3 
If cell.Value = "" Then cell.Clear 
Next 
End Sub
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "VBA Conditional Formatting in Microsoft Excel"
Thread Thread Starter Forum Replies Last Post
Conditional formatting not getting updated automatically in Excel 2010 Vento Windows Software 3 28-04-2012 07:32 AM
Excel Macros for conditional formatting statement in Excel Kungfu Pandey Microsoft Project 1 08-01-2012 07:35 PM
Microsoft Excel 2007 formatting Meditation Windows Software 4 29-01-2011 03:58 PM
Writing a conditional formatting with Excel 2007 KAMAL60 Software Development 5 23-10-2009 06:19 PM
Conditional formatting in excel RockeЯ Windows Software 4 27-06-2009 12:06 PM


All times are GMT +5.5. The time now is 05:06 PM.