Need correction in code, little correction..

[VBABEGINER]

Need correction in code, little correction..
Error - Object doesn't support this property or method..

With Worksheets("Sheet1")
    For i = 3 To RowCnt1
    If .HorizontalAlignment = xlLeft Then[COLOR=#ff0000]............Getting an error on this[/COLOR]
    .Cells(i, 6).Interior.Color = vbRed
    End If
    Next i
End With

 

[Joe4]

I think you are missing the Range reference on this line:

If .[COLOR=#ff0000]HorizontalAlignment[/COLOR] = xlLeft Then

What range are you trying to check the Horizontal Alignment on?

Note how you correctly have a range reference in the line below:

   [COLOR=#ff0000] .Cells(i, 6).[/COLOR]Interior.Color = vbRed

 

[VBABEGINER]

Apologies. It's my bad. Forgot to use, cell reference. Thank you for this..
My code run's, but cell is not highlight in red color...can you tell me why is so?

I think you are missing the Range reference on this line:

If .[COLOR=#ff0000]HorizontalAlignment[/COLOR] = xlLeft Then

What range are you trying to check the Horizontal Alignment on?

Note how you correctly have a range reference in the line below:

   [COLOR=#ff0000] .Cells(i, 6).[/COLOR]Interior.Color = vbRed

 

[Joe4]

It works fine for me.
Note that the HorizontalAlignment is checking the formatting setting (not really looking at the data).
If you enter text into an unformatted cell, the text is left-justified by default (numbers are right-justified by default), but that does not mean the horizontal setting is set to "Left". By default, it is set to "General".
If you want to check if it is either "Left" or "General", the code would look like this:

    For i = 3 To RowCnt1
        With Worksheets("Sheet1").Cells(i, 6)
            If (.HorizontalAlignment = xlLeft) Or (.HorizontalAlignment = xlGeneral) Then
                .Interior.Color = vbRed
            End If
        End With
    Next i

If that doesn't do what you want, you are going to explain to us exactly what it is that you want to happen.

 

[VBABEGINER]

Hi Joe, Really thanks for this explanation..!! Im really not so clever to understand this "pointers". The code makes all the dates in "red".

Actually what happens is, in my one of the date column, one date is coming in wrong format..
Date is -

20/01/13

I have just cross check with Year formula and I got surprised answer that 2020 in answer. Which is in-correct. Year should come 2013.

Therefore, Im trying code like,

For i = 3 To RowCnt1
If Len(Right(Cells(i, 6), 4)) < 4 Then
Cells(i, 6).Interior.Color = vbRed
Else
End If
Next i

...but not working...I really cant catch the logic..

It works fine for me.
Note that the HorizontalAlignment is checking the formatting setting (not really looking at the data).
If you enter text into an unformatted cell, the text is left-justified by default (numbers are right-justified by default), but that does not mean the horizontal setting is set to "Left". By default, it is set to "General".
If you want to check if it is either "Left" or "General", the code would look like this:

    For i = 3 To RowCnt1
        With Worksheets("Sheet1").Cells(i, 6)
            If (.HorizontalAlignment = xlLeft) Or (.HorizontalAlignment = xlGeneral) Then
                .Interior.Color = vbRed
            End If
        End With
    Next i

If that doesn't do what you want, you are going to explain to us exactly what it is that you want to happen.

 

[VBABEGINER]

The main thing is,

20/01/13

this date is wrong coming in my answer. And im trying to highlight this cell in red. That this wrong capture..so user can understand

 

[Joe4]

Do you need VBA code?
Because this can be done pretty easily with Conditional Formatting.
It sounds to me like you are expecting date values, and some are coming over as text, because they are not in a valid date format (hence, the left-justification because they are text).
Better to just use the ISTEXT function.

So in Conditional Formatting, if checking cell F3, the Conditional Formatting formula would just look like:

=ISTEXT(F6)

and then choose your red coloring.

If you need this in VBA code, you can turn on the Macro Recorder and record yourself applying the Conditional Formatting.

 

[VBABEGINER]

Hi, can you please give me this in vba code..Coz my entire other are in vba code..

Do you need VBA code?
Because this can be done pretty easily with Conditional Formatting.
It sounds to me like you are expecting date values, and some are coming over as text, because they are not in a valid date format (hence, the left-justification because they are text).
Better to just use the ISTEXT function.

So in Conditional Formatting, if checking cell F3, the Conditional Formatting formula would just look like:

=ISTEXT(F6)

and then choose your red coloring.

If you need this in VBA code, you can turn on the Macro Recorder and record yourself applying the Conditional Formatting.

 

[Joe4]

Hi, can you please give me this in vba code..Coz my entire other are in vba code..

Did you try what I recommended here, regarding VBA code?

If you need this in VBA code, you can turn on the Macro Recorder and record yourself applying the Conditional Formatting.

 

[VBABEGINER]

Ohh, aplogies...I did not notice your line..Let me try myself..

Did you try what I recommended here, regarding VBA code?