Solved

Fill with background color

Posted on 2015-02-22
11
192 Views
Last Modified: 2016-02-10
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
0
Comment
Question by:HuaMinChen
  • 6
  • 5
11 Comments
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40625174
BTW, when running "CopyFromRecordset" within VBA codes, is there any way to get known how many records have been retrieved?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40625190
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40625192
>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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40625195
what should be the syntax of rowcount? Thanks
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40625201
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 10

Author Comment

by:HuaMinChen
ID: 40625370
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40625450
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40627331
Thanks. One last thing, what to assign to the cell if I expect to bracket the amount, when its value is negative?
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40627501
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 40627574
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40627603
but what do you want? without the minus sign?
in which case you just remove the " - " from the format
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
The viewer will learn how to count occurrences of each item in an array.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now