Search code examples
excelvbaconditional-formatting

Excel Conditional Formatting is incorrect when value is entered by VBA Code

i got some Question marks over my head with this problem and hope you can help me out with this.

In 2 Columns i got some Numbers formatted as text, for example "1.234". This values are entered after an Button click from the VBA Code. After this the first Cell also got some Conditional Formatting to show a red or green BG if the Values are equal to each other or not.

Conditional Formatting looks like:

With Sheet2.Range(Sheet2.Cells(5, find_column.Column), Sheet2.Cells(intTotalRows, find_column.Column))
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=RC" & find_column_compare.Column
    .FormatConditions(1).Font.Color = -16752384
    .FormatConditions(1).Interior.Color = 13561798
    .FormatConditions(1).StopIfTrue = True
End With
With Sheet2.Range(Sheet2.Cells(5, find_column.Column), Sheet2.Cells(intTotalRows, find_column.Column))
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=RC" & find_column_compare.Column
    .FormatConditions(2).Font.Color = -16383844
    .FormatConditions(2).Interior.Color = 13551615
    .FormatConditions(2).StopIfTrue = True
End With

So this works as expected, if its the same number e.g. 1.234 this Cell is green and if not its red.

If i change the cell value manually to anything else the BG color switches as expected between red and green. But the point of this is not to enter the end number manually, so i got this line of code for the calculation:

Cells(active_cell_row, find_column.Column) = Round(Cells(active_cell_row, Col - 1) / Cells(active_cell_row, Col), 3)

This enters the right number to the cell, but the conditional Formatting is always red even if the values are the same. If i overwrite the same value manually the conditional formatting switches to green...

Solution

  • Ok Never mind i fixed this by myself..

    If anyone got this problem:

    First declare the value as an string...

    Dim txt As String: txt = Round(Cells(active_cell_row, Col - 1) / Cells(active_cell_row, Col), 3)
Cells(active_cell_row, find_column.Column) = txt