Conditional Formatting using VBA ...continued...

[Eloise T]

This UDF may help you too

Function HexToDec(hex As String) As Double
    Dim j              As Variant
    Dim hexArray()      As Double
    Dim i              As Long
    Dim k              As Long
    Dim n              As Long

    n = Len(hex)
    k = -1
    ReDim hexArray(1 To n)
  
    For i = n To 1 Step -1
        j = Mid(hex, i, 1)
        k = k + 1
        Select Case j
            Case 0 To 9
                hexArray(i) = j * 16 ^ (k)
            Case Is = "A"
                hexArray(i) = 10 * 16 ^ (k)
            Case Is = "B"
                hexArray(i) = 11 * 16 ^ (k)
            Case Is = "C"
                hexArray(i) = 12 * 16 ^ (k)
            Case Is = "D"
                hexArray(i) = 13 * 16 ^ (k)
            Case Is = "E"
                hexArray(i) = 14 * 16 ^ (k)
            Case Is = "F"
                hexArray(i) = 15 * 16 ^ (k)
        End Select
    Next i
  
    HexToDec = Application.WorksheetFunction.Sum(hexArray)
End Function

Thank you! I'll pour through this after hours so I understand how you "constructed" this.

 

[Eloise T]

Hi ,

I am doing the job for the first rule ; you can follow the same principle for all the others.

Define a named range to cater to the search expression ; I have defined the named range SearchFor , and in the Refers To box , I have entered :

={"D70","E70","KDL70","LC70","LC-70","M70","PNC70","PNL70","PNR70"}

The CF rule is now :

=OR(ISNUMBER(SEARCH(SearchFor,$C3)))

This is the rule that will have to be implemented in VBA ; this I have done.

There is still one problem , which is that when you run the macro , the CF rule is being added but is not being applied. I have read about this on the net , but I don't have the time at present to go through it in detail.

What I have seen is that if you click on Conditional Formatting , edit the rule and then press OK without making any changes , the rule is applied.

If you can check it out , please do so , otherwise I will come back on this in the next few days.

Narayan

Good news! I created a Name Manager (Duh! ...why didn't I think of that sooner?!) for the √ and the ≠ instead of trying to get the Chr or ChrW formula to work. Excel Name Manager happily accepted the two symbols, as is, without having to figure out the ASCII or Hex that VBA was insisting upon.

I appreciate all your help and wisdom (and others) through this trek (for me) through the VBA wilderness!

My only problem left is figuring out the code for a gradient-shaded color scheme, but I will Post a New Thread for that.

 

[Eloise T]

This UDF may help you too

Function HexToDec(hex As String) As Double
    Dim j              As Variant
    Dim hexArray()      As Double
    Dim i              As Long
    Dim k              As Long
    Dim n              As Long

    n = Len(hex)
    k = -1
    ReDim hexArray(1 To n)
  
    For i = n To 1 Step -1
        j = Mid(hex, i, 1)
        k = k + 1
        Select Case j
            Case 0 To 9
                hexArray(i) = j * 16 ^ (k)
            Case Is = "A"
                hexArray(i) = 10 * 16 ^ (k)
            Case Is = "B"
                hexArray(i) = 11 * 16 ^ (k)
            Case Is = "C"
                hexArray(i) = 12 * 16 ^ (k)
            Case Is = "D"
                hexArray(i) = 13 * 16 ^ (k)
            Case Is = "E"
                hexArray(i) = 14 * 16 ^ (k)
            Case Is = "F"
                hexArray(i) = 15 * 16 ^ (k)
        End Select
    Next i
  
    HexToDec = Application.WorksheetFunction.Sum(hexArray)
End Function

How do you "load" this? I pasted the code into ALT+F11 → F7, but it did not appear as a macro when I pressed ALT+F8.

 

[NARAYANK991]

How do you "load" this? I pasted the code into ALT+F11 → F7, but it did not appear as a macro when I pressed ALT+F8.

Hi ,

A UDF (User Defined Function) is used exactly the way you would use any native Excel function such as SUM.

Just as you would write a worksheet cell formula :

=SUM(J7:J13)

so also you can write a worksheet cell formula which uses a UDF :

=HEXTODEC("22BA")

The result of the above conversion , 8890 , will be displayed in the cell where you have entered the above formula.

I am not sure how this UDF is different from the native Excel function HEX2DEC , which when called through :

=HEX2DEC("22BA")

will display 8890.

Narayan

 

[Eloise T]

I understand now. Makes perfect sense. Thank you!