Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Fill with background color

Posted on 2015-02-22
11
Medium Priority
?
205 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 11

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 143

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 11

Author Comment

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40625201
0
 
LVL 11

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 143

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 11

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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 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 11

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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to count occurrences of each item in an array.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Suggested Courses

783 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