Fill with background color

Hi,
I can see the background color of the cells is like the attached. How to put the same background color to other cells, using VBA codes?
t991.png
LVL 11
HuaMin ChenSystem AnalystAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
to set the color of a cell (range), you use:
cell.Interior.Color = vbGreen
or any other RGB color index
cell.Interior.Color = RGB(127, 187, 199)
0
 
HuaMin ChenSystem AnalystAuthor Commented:
BTW, when running "CopyFromRecordset" within VBA codes, is there any way to get known how many records have been retrieved?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>when running "CopyFromRecordset" within VBA codes, is there any way to get known how many records have been retrieved?

only from checking the recordset property (rowcount) after the action;
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
HuaMin ChenSystem AnalystAuthor Commented:
what should be the syntax of rowcount? Thanks
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
HuaMin ChenSystem AnalystAuthor Commented:
Hi,
I'm retrieving records from AS 400, using these

    Dim cn As ADODB.Connection
    Dim strCNT As String, strSQL As String, strSQL2 As String, strSQL3 As String, strSQL4 As String, strSQL5 As String, strSQL6 As String
    Dim cnt As Integer, rw As Integer
    Dim rs4 As ADODB.Recordset
    Dim rs5 As ADODB.Recordset
    Dim rs6 As ADODB.Recordset
    
    strCNT = IS5ConnectionString()
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionTimeout = SQL_TIMEOUT
    cn.Open strCNT
    
    strSQL4 = "SELECT SAAB,SAAN,SAAS,SAAPP,SABRNM,c.scact,SAPOD,SAPBR,SAPSQ,SAVFR,SADRF,SATCD,SACCY,SAAMA FROM " & HOST_LIB_EQ & "." & TBL_FM & " a," & HOST_LIB_EQ & "." & "scpf c where length(ltrim(rtrim(cast(SAPOD as char(12)))))=7 and substring(ltrim(rtrim(cast(SAPOD as char(12)))),2,6)='150130' and ltrim(rtrim(SACCY))='USD' and ltrim(rtrim(a.saan))=ltrim(rtrim(c.scan)) and ltrim(rtrim(a.saas))=ltrim(rtrim(c.scas)) and ltrim(rtrim(SAPBR))='@@AR'"
    
    Set rs4 = CreateObject("ADODB.Recordset")
    rs4.Open strSQL4, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    Worksheets("EQ System Gen FX").Cells.ClearFormats
    Worksheets("EQ System Gen FX").Cells(2, 1).Value = "Overnight Position Revaluation"
    Worksheets("EQ System Gen FX").Range("A5").CopyFromRecordset rs4
    cnt = rs4.RecordCount
...

Open in new window


I don't know why cnt in above does return "-1", while I can see there are 18 records retrieved already.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I guess this is due to:
adOpenForwardOnly

which may have the effect of not filling that property
I don't see any other solution ...
0
 
HuaMin ChenSystem AnalystAuthor Commented:
Thanks. One last thing, what to assign to the cell if I expect to bracket the amount, when its value is negative?
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the format needs to be like this:
#.##0;(-#.##0)

in vba, you assign that to the numberFormat property of the range:

  Dim r As Range
  Set r = Range("B:B")
  r.NumberFormat = "#.##0;(-#.##0)"

Open in new window

0
 
HuaMin ChenSystem AnalystAuthor Commented:
Many thanks. I put these

    Range("N" & (11 + cnt + cnt2)).NumberFormat = "#.##0;(-#.##0)"
    Range("N" & (11 + cnt + cnt2)).Formula = "=Sum(N" & (11 + cnt) & ":N" & (10 + cnt + cnt2) & ")"
...

Open in new window


but it is showing

(-9278769.0)

which is confusing, right?
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
but what do you want? without the minus sign?
in which case you just remove the " - " from the format
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.