Replace 2

[komark]

Hi,
I have the following Code:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight

Application.ScreenUpdating = False
For x = 1000 To 1 Step -1
If Range("C" & x).Font.Bold Then
Rows(x) = "R"
End If

Next

This code is supposed to shift all the data from Column A to C. Check if anything in Column C is bold. If there is than put an R in cell under Column A. My program is putting R in the entire row. How do i fix this?
Please Help.

 

[SkipVought]

Hi,

    For x = 1000 To 1 Step -1
      with Range("C" & x)
        If .Font.Bold Then
            .Value = "R"
        End If
      end with
        
    Next

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[PHV]

Replace this:
Rows(x) = "R"
with this:
Cells(x, "A") = "R"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[komark]

Thanks a lot guys. It works great. But what if i want to color the cell to red? What do i do?

 

[PHV]

No VBA needed for that: have a look at Condtional formatting.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[komark]

PHV: Can't it be done by VBA. it would help me if I do it in VBA

 

[SkipVought]

Why do it in VBA, when there's built-in functionality to do it in Excel via COnditional Formatting as PHV has suggested.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[komark]

I wanted to automate the whole process of post proccessing the data from project in excel. Like changing the date value, cells filling etc. Then I will feed the data into a gantt chart builder.

 

[SkipVought]

How does using Conditional Formating NOT fit into that approch. It's about as automatic as you can get!


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[komark]

Skip,
I guess I am doing the formatting the wrong way.
Under conditional formatting>
condition 1
if cell value is equal to YES
condition 2
cell value is equal to Cell fill RED

 

[SkipVought]

[tt]
condition 1:
if cell value is equal to YES
FORMAT 1: ???
condition 2:
if cell value is equal to ???
FORMAT 2: fill RED
[/tt]


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[komark]

Skip,
If cell value is YES than i wanted to fill another cell the same row: RED. Is this what your conditions doing?

 

[SkipVought]

It can do that.

Select the column of cells you want to format. Let's assume its f4:f999. Let's assume that the YES's are in column H

in Condition 1:
[tt]
=H4="YES"
[/tt]
Format 1: is red


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}