Previous Topic Next Topic

Conditional Formatting VB

Closed
nicola1987 Posts 1 Registration date Wednesday 24 April 2013 Status Member Last seen 24 April 2013 - 24 Apr 2013 à 06:19
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 7 May 2013 à 11:15
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

Related:

1 response

Réponse 1 / 1
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
27 Apr 2013 à 10:26
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
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
29 Apr 2013 à 10:25
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
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
2 May 2013 à 08:17
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)
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
7 May 2013 à 11:15
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

Similar discussions

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

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

26 responses
Conditional Format Cells Based on Another Cell and Own Cell
Elaine -
x -

1 response
Conditional formatting based text in another cell
y88y -
y88y -

2 responses
Conditional Formatting - highlight overdue dates
TanyaRT -
TanyaRT -

2 responses