Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing conditional 2 cells and color (excel 2003)

    Hi,

    I'm new on this lounge and need some assistance. I have a year of spreadsheet data ( hour data ) from Wind turbines and needs to exclude non-availability data from it. This means comparing 2 columns pro Wind Turbine. eg power production with wind speed, ( column C to Q with column U to AC). If power is zero and wind speed is greater than 4, I need coloring and or copy that data set to a seperate sheet. Attached a sample sheet with rows 113 to 136 marked. I will execute this action into a macro. ( the function Sum conditional into 2 columns is a way it should work but instead of summing I want my desired action )

    Thanks,

    Ton O

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing conditional 2 cells and color (excel 2003)

    Hello Ton,

    Welcome to Woody's Lounge!

    You could use Conditional Formatting to color the cells, but since you asked for a macro, see if this macro does what you want:

    Sub MarkAnomalies()
    Dim lngMaxRow As Long
    Dim lngRow As Long
    Dim lngCol As Long
    Application.ScreenUpdating = False
    Range("A5").CurrentRegion.Font.ColorIndex = 1
    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngCol = 3 To 16 ' C to Q
    For lngRow = 5 To lngMaxRow
    If Cells(lngRow, lngCol) = 0 And Cells(lngRow, lngCol + 18) > 4 Then
    Cells(lngRow, lngCol).Font.ColorIndex = 3
    Cells(lngRow, lngCol + 18).Font.ColorIndex = 3
    End If
    Next lngRow
    Next lngCol
    Application.ScreenUpdating = True
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Comparing conditional 2 cells and color (excel 2003)

    I am not sure exactly what you are after. Could you elaborate. I don't understand why rows 151 and 152 do not meet the criteria: K=0, Y>4. What do you want the output to look like: what should be copied? Also are the columns in paired sets?

    I think an easy way would be without the macro:
    Just put on an autofilter (data - filter - autofilter)
    Filter on the one column to be = 0 and the other to be >4

    You could set the color and copy the range as desired to a new location. Doing it this way, involves no coding and less work to debug, maintain and change.

    Coloring could also be done using conditional formatting.

    Steve

  4. #4
    New Lounger
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing conditional 2 cells and color (excel 2003)

    Hans,

    Thanks it does what was intended. First time some problems due to hidden columns but after deleting and some modification it works.

    Bedankt,

    Ton O

  5. #5
    New Lounger
    Join Date
    Jul 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing conditional 2 cells and color (excel 2003)

    Steve,

    I do already some filtering but with 12 spreadsheets and more than 8000 datalines and 9 Windturbines and changing criteria I don't want to this manually. To do it by filter I should do a lot of actions which should be controlled.

    Thanks for your response. I think I can go further with the suggestions from Hans.

    Regards,

    Ton O

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •