• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting the difference between two values and highlighting the difference if the value is less or greater than zero

trividha

New Member
Hi

I am trying to write a macro code where i have two columns namely BU and CD. In column CE i want the difference of cell CD and BU. i.e.,
CE4= CD4- BU4
CE5 = CD5 - BU5 and so on.. Till the last used row of both the columns which has equal range.

I also want to highlight the rows in which value of CE is either Greater than 0 or less than 0 or N/A.

Thanks in advance.
 
I am new to VBA and this is what I have come up with this far but the code isn't working..

Code:
        Dim n As Integer
        Dim valE As Double
        Dim valI As Double
        Dim i As Integer
    
       
       
        Dim outputSheet As Worksheet
       
        Set outputSheet = ThisWorkbook.ActiveSheet

    
                
        n = outputSheet.Range("CD4:CD").Cells.SpecialCells(xlCellTypeConstants).count
        Application.ScreenUpdating = False

      For i = 4 To n
       valE = outputSheet.Range("CD4" & i).Value
       valI = outputSheet.Range("BU4" & i).Value

          If valE = valI = 0 Then

           Else:

          outputSheet.Range("CD4" & i).Font.Color = RGB(255, 0, 0)

           End If
       Next i
 
Last edited by a moderator:
Hi !​
You should attach a sample workbook according to your code & need …​
But using easy formula and conditional formatting, you ever don't need any code !​
 
I want to do it in a number of worksheets. That's why i need to write a macro. Here i am attaching the sample excel files.
In this excel sheet H column should have the difference value of G & E.
 

Attachments

  • sample.xlsx
    12.6 KB · Views: 2
  • sample_1.xlsx
    8.6 KB · Views: 1
According to the sample.xlsx attachment, as a beginner starter paste this demonstration to the Sheet1 worksheet module :​
Code:
Sub Demo()
    With [A1].CurrentRegion.Rows("2:" & [A1].CurrentRegion.Rows.Count).Columns
        .Item(8).NumberFormat = "[Blue]General;[Red]-General;General"
        .Item(8).Value2 = Evaluate(.Item(7).Address & "-" & .Item(5).Address)
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Hi..
Thanks for the code.. Its working for the sample file perfectly but is not showing any results in my main file.
Attaching the main file.
I have updated the code according to my main worksheet as follows:
Code:
Sub Demo()
    With [A2].CurrentRegion.Rows("4:" & [A2].CurrentRegion.Rows.Count).Columns
        .Item(24).NumberFormat = "[Blue]General;[Red]-General;General"
        .Item(24).Value2 = Evaluate(.Item(23).Address & "-" & .Item(15).Address)
    End With
End Sub
 

Attachments

  • AAM KHAS BAGH 2019-20_testrun.xlsm
    144.1 KB · Views: 2
Last edited by a moderator:
First, thanks to add code tags (or use the menu) to your previous post …​
Its working for the sample file perfectly but is not showing any results in my main file.
The usual issue when the attachment does not well reflect the real workbook ! …​
Your mod plays with columns O, W & X, are they the right columns ?! As you can replace the column index by a string …​
Your rows mod does not seem very accurate too.​
 
Back
Top