Previous Topic Next Topic

Conditional Formatting VB

Closed
nicola1987 Posts 1 Registration date Wednesday April 24, 2013 Status Member Last seen April 24, 2013 - Apr 24, 2013 at 06:19 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 7, 2013 at 11:15 AM
Hello,
I wonder if you can help me please?

I have an excel 2003 document which requires more than 3 conditional formats, i am using this piece of VB code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColCell As Integer
Dim iColText As Integer

If Not Intersect(Target, Range("b15:bm182")) Is Nothing Then
On Error Resume Next
Select Case Target
Case ""
iColCell = 0
iColText = 1
Target.Cells.Font.Bold = False
Case "O"
iColCell = 4
iColText = 4
Target.Cells.Font.Bold = True
Case "W"
iColCell = 37
iColText = 37
Target.Cells.Font.Bold = True
Case "I"
iColCell = 41
iColText = 41
Target.Cells.Font.Bold = True
Case "DR"
iColCell = 15
iColText = 15
Target.Cells.Font.Bold = True
Case "C"
iColCell = 38
iColText = 38
Target.Cells.Font.Bold = True
Case "R"
iColCell = 45
iColText = 45
Target.Cells.Font.Bold = True
Case "DM"
iColCell = 27
iColText = 27
Target.Cells.Font.Bold = True
Case "D"
iColCell = 15
iColText = 15
Target.Cells.Font.Bold = True

Case Else
'Whatever

End Select
Target.Interior.ColorIndex = iColCell
Target.Cells.Font.ColorIndex = iColText

End If
End Sub

However the cells I am wanting to conditionally format hold and IF function in them and whenever I paste the formula of the cell into the cases in the VB it doesnt like it.
Is there a way I can use this code for cells that contain a formula?

Thankyou Very Kindly in Advance

Nicola

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Apr 27, 2013 at 10:26 AM
I tired with this formula in cell b17
=IF("a"="a","DR",4)
and i did not get any error. If you can explain how you are able to replicate the issue, one can see what one can do
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Apr 29, 2013 at 10:25 AM
Hi Rizvisa,

I tried to solve this one but couldn't figure out an answer myself. I thought Worksheet_Calculate would be the answer. But how do you refer to the recalculated cell? I don't know.

To recreate the issue:
B17: =IF(A17="a","DR",4)
A17: a

Curious to see how you would solve this query.

Best regards,
Trowa
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 2, 2013 at 08:17 AM
Thanks Trowa for making me understand the issue. I see in theory three ways (none tested as far)

1. on cell change, loop thru each and see if color needs to be updated (thats do able for sure)

2. instead of =if(...), have a custom function to do that and have formula marked as volatile (need to see at what cost)

3. have in some hidden location cells that has a custom function marked as volatile. have those formula apply condition (need to see at what cost)
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 7, 2013 at 11:15 AM
Custom functions and me aren't very acquainted yet. But the first option I can do and is actually the one I thought of, but for that big of a range it is going to take a while..

Maybe when you have the time you can write a custom function for us (me).

In the meantime here is a working code for Nicola: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.ScreenUpdating = False

For Each cell In Range("b15:bm182")
    If cell.Value = vbNullString Then
        cell.Interior.ColorIndex = 0
        cell.Font.ColorIndex = 1
        cell.Font.Bold = False
    ElseIf cell.Value = "O" Then
        cell.Interior.ColorIndex = 4
        cell.Font.ColorIndex = 4
        cell.Font.Bold = True
    ElseIf cell.Value = "W" Then
        cell.Interior.ColorIndex = 37
        cell.Font.ColorIndex = 37
        cell.Font.Bold = True
    ElseIf cell.Value = "I" Then
        cell.Interior.ColorIndex = 41
        cell.Font.ColorIndex = 41
        cell.Font.Bold = True
    ElseIf cell.Value = "DR" Then
        cell.Interior.ColorIndex = 15
        cell.Font.ColorIndex = 15
        cell.Font.Bold = True
    ElseIf cell.Value = "C" Then
        cell.Interior.ColorIndex = 38
        cell.Font.ColorIndex = 38
        cell.Font.Bold = True
    ElseIf cell.Value = "R" Then
        cell.Interior.ColorIndex = 45
        cell.Font.ColorIndex = 45
        cell.Font.Bold = True
    ElseIf cell.Value = "DM" Then
        cell.Interior.ColorIndex = 27
        cell.Font.ColorIndex = 27
        cell.Font.Bold = True
    ElseIf cell.Value = "D" Then
        cell.Interior.ColorIndex = 15
        cell.Font.ColorIndex = 15
        cell.Font.Bold = True
    End If
Next cell

Application.ScreenUpdating = True
End Sub
0

Similar discussions

How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
Highlight cell if another cell contains any text
LvH -
LvH -

4 responses
Conditional formating - apply on more cells
Biljana -
Raj -

32 responses
one cell will turn green when the two other cells match
diestra123 -
diestra123 -

13 responses