Conditional Formatting VB

[Closed]
Report
Posts
1
Registration date
Wednesday April 24, 2013
Status
Member
Last seen
April 24, 2013
-
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
-
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 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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)
Posts
2817
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 14, 2021
486
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