CODEWITHSUNDEEP
excelvba
Jake Kurdys
Jake Kurdys

Reputation: 11

If Cell.Value > (Greater Than) function not working to color cells

The code that Im currently using. 

Dim iRowCq As Range
Set iRowCq = Range("B2:DJ26")

For Each Cell In iRowCq

    If Cell.Value > "0.3" or cell.value > 0.3 Then
        Cell.Interior.Color = RGB(0, 255, 0)
    End If

Next

I want to color cells that are greater than 0.3 green. Yet while 99% of the cells work, there are sometimes cells that are greater than 0.3 and are not colored. Ive even tried rounding the data to two decimal points before which did not work. Could someone please help?

Screen shot of excel sheet

Upvotes: 0

Views: 953

Answers (3)

Dominique
Dominique

Reputation: 17565

Why are you re-inventing conditional formatting? It can be done in a very simple way, without needing VBA:

enter image description here

Upvotes: 1

Error 1004
Error 1004

Reputation: 8230

You could try the below:

Option Explicit

Sub test1()

    Dim rng As Range, cell As Range

    With ThisWorkbook.Worksheets("Sheet1") '<- It s better practise to specify the worksheet

        Set rng = .Range("B2:DJ26") '<- This range does not cover your whole spreadsheet. As i can see from the image the last column IS NOT "D" BUT "S" and the last row IS NOT 26 BUT 25. Setting range to *.Range("B2:SJ25")* we cover your range.

        For Each cell In rng

            If CDec(cell.Value) > 0.3 Then '<- Convert the cell value into decimal in order to compare with decimal
                cell.Interior.Color = RGB(0, 255, 0)
            End If

        Next

    End With

End Sub

Upvotes: 0

user11217663
user11217663

Reputation:

You cannot combine string and numerical comparisons like that for greater than/less than comparisons. A string might not be greater than "0.3" as a string but it will always be sorted higher than 0.3 as a number.` A combination string and numerical comparison might work on a strictly equals to basis.

Take the numerical value of either a string or a mixed number and use that for coparison.

Dim iRowCq As Range Set iRowCq = Range("B2:DJ26")

For Each Cell In iRowCq

    If val(Cell.Value) > 0.3 Then
         Cell.Interior.Color = RGB(0, 255, 0)
    End If

Next

Upvotes: 1

Related Questions